Skip site navigation (1) Skip section navigation (2)

Re: dropped columns and ms access

From: "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch>
To: <pgsql-odbc(at)postgresql(dot)org>
Cc: "David P(dot) Lurie" <dbase4(at)hotmail(dot)com>
Subject: Re: dropped columns and ms access
Date: 2004-06-25 06:56:10
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-odbc

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"), _
            db.TableDefs.Append tbl
            Set tbl = db.TableDefs(strTblName)
            tbl.Connect = strConn
        End If
    CreateODBCLinkedTables = True
    MsgBox "Links are ok. Please restart MS Access.", vbInformation
    Exit Function
   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 permissions that work.

Philippe Lang
Attik System

-----Message d'origine-----
De : pgsql-odbc-owner(at)postgresql(dot)org [mailto:pgsql-odbc-owner(at)postgresql(dot)org] De la part de David P. Lurie
Envoyé : vendredi, 25. juin 2004 08:03
À : pgsql-odbc(at)postgresql(dot)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?


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


pgsql-odbc by date

Next:From: apmDate: 2004-06-25 14:33:44
Subject: Re: unixODBC drivers for PostgreSQL?
Previous:From: David P. LurieDate: 2004-06-25 06:02:46
Subject: dropped columns and ms access

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group