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 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