Linked Tables not Saving Altered Connection Strings (1 Viewer)

[AdrianThorn]

So we have an Access front end that consists of several hundred linked tables. Periodic changes to the backend and simple user error has created a situation where I needed a function that would delete out of date versions of the linked tables and relink them, verifying that the appropriate username and password info was saved in the connection string.

I have no idea why the code isn't working. According to Debug.Print the connection strings are being updated with the correct login info but when I look at the table properties it doesn't reflect the changes made. Any ideas whats going on?

    Dim db As Database
    Dim tdef As TableDef
    Dim tablesname As String
    Dim tableaname As String
    Dim constring As String

    Set db = CurrentDb
    last_connection = db.TableDefs.count - 1

    For i = 0 To last_connection
        
        If (Nz(InStr(db.TableDefs(i).Connect, "DRIVER={PostgreSQL ANSI};DATABASE=odk_aggregate;SERVER=omitted;PORT=5432;"), 0) <> 0) And Left(db.TableDefs(i).SourceTableName, 6) = "alb_cr" Then
            constring = db.TableDefs(i).Connect
            
            If Nz(InStr(constring, "UID=omitted;PWD=omitted;"), 0) = 0 Then
                    constring = Replace(constring, "DRIVER={PostgreSQL ANSI};DATABASE=odk_aggregate;SERVER=omitted;PORT=5432;", "DRIVER={PostgreSQL ANSI};DATABASE=odk_aggregate;SERVER=omitted;PORT=5432;UID=omitted;PWD=omitted;")
            End If
            Debug.Print constring
            tablesname = db.TableDefs(i).SourceTableName
            tableaname = db.TableDefs(i).Name
            
            DoCmd.DeleteObject acTable, db.TableDefs(i).Name
            
            Set tdef = db.CreateTableDef(tableaname)
            tdef.Connect = constring
            tdef.SourceTableName = tablesname
            Debug.Print tdef.Connect
            db.TableDefs.Append tdef
            db.TableDefs.Refresh
        
        End If

    Next
    MsgBox "Done."

 

[static]

"several hundred linked tables" Respect!

You are trying to delete the tables. I'm not sure that is necessary just to update the connection, but obviously it will fail if the table is open and I see no error handling in your code.

 

[AdrianThorn]

Well, I chose to delete and rebuild the table definitions because refreshing the links was for some reason causing added or deleted columns to appear on the table.

I stripped out the error handling for the time being. The fact that the procedure doesn't fail is partly what has me stumped.

 

[static]

I always meant to experiment with passthrough queries and file DSN's but it never happened so I'm no expert.

I seem to recall not being able to reset connections to SQL server databases using tabledef.connect (I have no idea what PostgreSQL is, maybe it's a similar thing), so all I can say is good luck and let us know if you work it out.

Hopefully somebody else here can help.

 

[Galaxiom]

Are you looking at the ConnectionString in the tooltip when you hover over the table in the list?

This is not updated until you run:

Application.RefreshDatabaseWindow

 

[AdrianThorn]

Nope. MsysObjects.

 

[DavidAtWork]

The method I've always used is to have a table of tablenames you want to link, open a recordset of the tablenames and loop through, relinking the tables using a connection string, your connection string can be defined as a constant variable. No need to worry about table definitions and any design changes to tables, the connection string can be DSN-less so no need to worry about any ODBC issues on the local machine.
David