Strange problem refreshing ODBC Connect (1 Viewer)

Royce

Access Developer
Local time
Today, 08:51
Joined
Nov 8, 2012
Messages
98
I have some code for refreshing links that I thought was working. I did make some minor modification to handle the case where the front end and back end names were different. Now for some reason the ODBC Connect strings are not getting refreshed, even on the tables where the names are the same.

The code, see below, renames the old table, creates a new table link, then deletes the original link. The code works, in that the tables are correctly linked. But when I use the Connect string later in other code it is the old string!

When I trace through the code I see where it changes the .Connect property, and newConnectionString has the correct value. But even after the db.TableDefs.Refresh line the table still has the old connection string!

What am I missing?

Code:
Public Sub RefreshODBCLinks(strTableName As String, newConnectionString As String, strSourceName As String, lngAttributes As Long)
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Dim originalname As String
    Dim tempname As String
    Dim sourcename As String
    Dim cleanName As String
    
    Dim i As Integer

    On Error GoTo Proc_Err

    Set db = CurrentDb
    

    ' Create new tables using the given DSN after moving the old ones '
   
    originalname = strTableName
    tempname = "~" & originalname & "~"
        
    ' Create the replacement table '
    db.TableDefs(originalname).Name = tempname
    cleanName = Replace(originalname, "~", vbNullString)
    Set tb = db.CreateTableDef(cleanName)
    
    If lngAttributes <> 0 Then
        tb.Attributes = lngAttributes
    End If
    tb.SourceTableName = strSourceName
    tb.Connect = newConnectionString
    
    db.TableDefs.Append tb
    
      
        
      Err = 0
      On Error Resume Next
      tb.RefreshLink         ' Relink the table.
      If Err <> 0 Then
        MsgBox "Unable to Relink table " & tb.Name & vbCrLf & vbCrLf & _
          "Some tables may have been relinked.  Database link condition is unknown."
        mbolRelink = False
        GoTo Proc_Exit
      End If
      
    ' delete the old table '
    DoCmd.DeleteObject acTable, tempname
    db.TableDefs.Refresh
    'Debug.Print "Refreshed ODBC table " & originalname
    
    

Proc_Exit:
   On Error Resume Next
   Set db = Nothing
   Exit Sub

Proc_Err:
  mbolRelink = False
  MsgBox "Error frmRelink function 'Relink' " & vbCrLf & vbCrLf & Error$
  Resume Proc_Exit

End Sub
 

Orthodox Dave

Home Developer
Local time
Today, 13:51
Joined
Apr 13, 2017
Messages
218
Hi Royce,

I have been successfully using code to relink my databases for years, without knowing how it worked (it was a copy and paste job!)

But now I know more about VBA I can understand it, and looking through it carefully, nowhere does it create a new table or delete the old one.

The crucial part of the code that makes the change is:

Code:
If [TableDef].Connect <> "" Then
[TableDef].Connect = ";DATABASE=" & NewBackPath
[TableDef].RefreshLink
End If

It has never let me down. There is a lot more in it (such as progress meters, and it loops through all the tables etc) but the crucial bit is above.
 

Royce

Access Developer
Local time
Today, 08:51
Joined
Nov 8, 2012
Messages
98
In most cases what you show is all that is needed, and I've used that for years also.

But I have a development situation where we need to frequently relink between a backend Access database using Jet and a backend SQL Server via ODBC. Because of a quirks in how Jet handles the switch, and I don't remember the details, simply resetting the connection string does not work.

In my code, which has also been in use for years elsewhere, the essential lines are:

Code:
' Rename the old table
db.TableDefs(originalname).Name = tempname

' create a new table
Set tb = db.CreateTableDef(cleanName)
...
' Set the new connection string
tb.Connect = newConnectionStringdb.TableDefs.Append tb[B]

..
[/B]' Delete the old table
DoCmd.DeleteObject acTable, tempname

I found the problem.

It turns out I'm hitting the Access Cached connection problem that I thought the above code fixed. The new connection string is to the same server and database, but other parameters are changed. As a result the db.Append tb line throws away the new connection string and reuses the original. To work around it, I have to open the relinking form BEFORE any of the existing connections are used. Then it does what is expected. From what I can tell, there is not a good solution.
 
Last edited:

Orthodox Dave

Home Developer
Local time
Today, 13:51
Joined
Apr 13, 2017
Messages
218
Thanks Royce and Static. As I have only ever linked to other Access databases as the back end, this problem is new to me. If in future I need to link to SQL Server, I can revisit this thread, knowing there's a solution.
 

Users who are viewing this thread

Top Bottom