Re: dropped columns and ms access

Hello,

I'm not aware of a problem like that, really. Personnally, after each change to the backend, I relink all tables,
programmatically.Here is the code: 

-------------------------------------
Option Compare Database
Option Explicit

'*****************************************************************
' The DoesTblExist function validates the existence of a TableDef
' object in the current database. The result determines if an
' object should be appended or its Connect property refreshed.
'*****************************************************************
Function DoesTblExist(strTblName As String) As Boolean

    On Error Resume Next

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef

    Set db = CurrentDb
    Set tbl = db.TableDefs(strTblName)

    If Err.Number = 3265 Then   ' Item not found.
        DoesTblExist = False
        Exit Function
    End If

    DoesTblExist = True

End Function

'*****************************************************************
' This relinks everything
'*****************************************************************
Function CreateODBCLinkedTables() As Boolean

    On Error GoTo CreateODBCLinkedTables_Err

    Dim strTblName As String
    Dim strConn As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim login As DAO.Recordset
    Dim tbl As DAO.TableDef
    Dim strDSN As String

    ' ---------------------------------------------
    ' We create the ODBC connection
    ' ---------------------------------------------
    Set db = CurrentDb
    Set login = db.OpenRecordset("select * from tblLogin")

    DBEngine.RegisterDatabase global_dsn_name(), _
        "PostgreSQL", _
        True, _
        "Description=SQL - " & login("DataBase") & _
        Chr(13) & "Server=" & login("Server") & _
        Chr(13) & "Database=" & login("DataBase") & _
        Chr(13) & "Username=" & login("UID") & _
        Chr(13) & "Password=" & login("PWD")

    ' ---------------------------------------------
    ' We create / refresh table links
    ' ---------------------------------------------
    Set rs = db.OpenRecordset("select * from tblODBCDataSources")

    While Not rs.EOF

        strTblName = rs("LocalTableName")

        strConn = "ODBC;"
        strConn = strConn & "DSN=" & global_dsn_name() & ";"
        strConn = strConn & "APP=Microsoft Access;"
        strConn = strConn & "DATABASE=" & login("DataBase") & ";"
        strConn = strConn & "UID=" & login("UID") & ";"
        strConn = strConn & "PWD=" & login("PWD") & ";"
        strConn = strConn & "TABLE=" & rs("ODBCTableName")

        If (DoesTblExist(strTblName) = False) Then
            Set tbl = db.CreateTableDef(strTblName, _
                        dbAttachSavePWD, rs("ODBCTableName"), _
                        strConn)
            db.TableDefs.Append tbl
        Else
            Set tbl = db.TableDefs(strTblName)
            tbl.Connect = strConn
            tbl.RefreshLink
        End If

        rs.MoveNext

    Wend

    CreateODBCLinkedTables = True

    MsgBox "Links are ok. Please restart MS Access.", vbInformation

CreateODBCLinkedTables_End:
    db.Close
    Application.Quit
    Exit Function

CreateODBCLinkedTables_Err:
   MsgBox Err.description, vbCritical, "MS Access"
   Resume CreateODBCLinkedTables_End

End Function
-------------------------------------

I use it in conjunction with a local table called "tblODBCDataSources", made up of two columns:
- ODBCTableName    text
- LocalTableName    text (PK)

I also use another local table, called "tblLogin":
- UID            text (PK)
- PWD            text
- Database        text
- Server        text


I hope this will help you. After a relink, I restart MS Access, the only workaround I was able to find in order to have
permissionsthat work. 

------------------
Philippe Lang
Attik System


-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de David P. Lurie
Envoyé : vendredi, 25. juin 2004 08:03
À : pgsql-odbc@postgresql.org
Objet : [ODBC] dropped columns and ms access

psqlODBC 07.03.02

PostgreSQL 7.4.3 (Cygwin)

MS Access 2003

Win XP Pro

Is there any workaround for the psqlODBC problem of MS Access losing the ability to link to tables after dropping
columns,other than pg_dump and then reload? 

Thanks,

David P. Lurie




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match