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: 6C0CF58A187DA5479245E0830AF84F42080336@poweredge.attiksystem.ch (view raw or flat)
Thread:
Lists: pgsql-odbc
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 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?

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



Responses

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-2014 The PostgreSQL Global Development Group