Relinking ODBC Tables using VBA

[MMcCarthy]

As a lot of my projects involve using an odbc linked backend data source I have found this piece of code to be very useful. I usually trigger it to run on system startup like in AutoExec macro. It avoids any difficulties down the road with broken links and if the DSN name or database server changes then the only edit that needs to be made is to the connection string.

Function relinkTables()
Dim tdf As DAO.TableDef
 
    For Each tdf In CurrentDb.TableDefs
        ' check if table is a linked table
        If Len(tdf.Connect) > 0 Then
            tdf.Connect = "odbc connection string to the DSN or database"
            tdf.RefreshLink
        End If
    Next
 
End Function
 

Note: If you have other linked tables aside from those connected by odbc you would have to allow for them in the code.

As usual all advice, critique and enhancements welcome :)

Mary

[NeoPa]

I have one that works specifically for Access database linked tables. It may be worth including that and making it less specific :

'ReLink() Updates links of all tables that currently link to strDBName to point
'to strDBName in the strFolder folder (if specified, otherwise the same folder
'as the current database).
Public Sub ReLink(ByVal strDBName As String, _
                  Optional ByVal strFolder As String = "")
    Dim intParam As Integer, intErrNo As Integer
    Dim strOldLink As String, strOldName As String
    Dim strNewLink As String, strMsg As String
    Dim varLinkAry As Variant
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
 
    Set db = CurrentDb()
    If strFolder = "" Then strFolder = CurrentProject.Path
    If Right(strFolder, 1) = "\" Then _
        strFolder = Left(strFolder, Len(strFolder) - 1)
    strNewLink = strFolder & "\" & strDBName
    For Each tdf In db.TableDefs
        With tdf
            If .Attributes And dbAttachedTable Then
                varLinkAry = Split(.Connect, ";")
                For intParam = LBound(varLinkAry) To UBound(varLinkAry)
                    If Left(varLinkAry(intParam), 9) = "DATABASE=" Then Exit For
                Next intParam
                strOldLink = Mid(varLinkAry(intParam), 10)
                If strOldLink <> strNewLink Then
                    strOldName = Split(strOldLink, _
                                       "\")(UBound(Split(strOldLink, "\")))
                    If strOldName = strDBName Then
                        varLinkAry(intParam) = "DATABASE=" & strNewLink
                        .Connect = Join(varLinkAry, ";")
                        On Error Resume Next
                        Call .RefreshLink
                        intErrNo = Err.Number
                        On Error GoTo 0
                        Select Case intErrNo
                        Case 3011, 3024, 3044, 3055, 7874
                            varLinkAry(intParam) = "DATABASE=" & strOldLink
                            .Connect = Join(varLinkAry, ";")
                            strMsg = "Database file (%F) not found.%L" & _
                                     "Unable to ReLink [%T]."
                            strMsg = Replace(strMsg, "%F", strNewLink)
                            strMsg = Replace(strMsg, "%L", vbCrLf)
                            strMsg = Replace(strMsg, "%T", .Name)
                            Call MsgBox(Prompt:=strMsg, _
                                        Buttons:=vbExclamation Or vbOKOnly, _
                                        Title:="ReLink")
                            If intErrNo = 3024 _
                            Or intErrNo = 3044 _
                            Or intErrNo = 3055 Then Exit For
                        Case Else
                            strMsg = "[%T] relinked to ""%F"""
                            strMsg = Replace(strMsg, "%T", .Name)
                            strMsg = Replace(strMsg, "%F", strNewLink)
                            Debug.Print strMsg
                        End Select
                    End If
                End If
            End If
        End With
    Next tdf
End Sub

The reason it's much longer is that it has to handle errors, and recover from them. The meat of it is very similar. Maybe we could look at a version of the ODBC one that handles the various failure scenarios too. That would be a very useful article to link to I expect.

[MMcCarthy]

Nice one Ade, valuable addition

Mary

[Denburt]

Hello all, I thought I would throw my two cents in on this one. Correct me if I am mistaken but according to the following article if you want to improve performance I make sure that when I link to Access tables I make sure that the naming convention uses the 8.3 format. I know there is a knowledge base article on it (somewhere with a similar function) but I have a short function I call before I relink each of these Access tables.

Option Explicit
Option Compare Database
'Dim BkEnd As String
Declare Function GetShortPathName Lib "kernel32" _
            Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
           ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long
Function GetShortName(ByVal sLongFileName As String) As String
           Dim lRetVal As Long, sShortPathName As String, iLen As Integer
           'Set up a buffer area for the API function call return.
           sShortPathName = Space(255)
           iLen = Len(sShortPathName)
 
           'Call the function.
           lRetVal = GetShortPathName(sLongFileName, sShortPathName, iLen)
           'Remove unwanted characters.
           GetShortName = Left(sShortPathName, lRetVal)
 End Function

http://support.microsoft.com/kb/889588/

Use 8.3 file name conventions
Access calls the GetShortPathNameW function across the network on each append query if the database file name is longer than eight characters or if the database is located in a folder name that is longer than eight characters.

This behavior occurs with file names and folder names that are longer than the 8.3 file naming convention limits specify. Long file and folder names can increase the time that is required for the query to be completed. If the name of your database file or of the folder where your database is located is longer than eight characters, rename the file name or the folder name. The file and folder names must be no longer than eight characters, and the file name extension must be no longer than three characters. The following is an example of a database path that includes a short, 8.3 convention file and folder names:
\Folder_1\Folder_2\AccessDb.mdb
The following is an example of a database path that uses long file and folder names:
\FolderForFirstDatabase\FolderForSecondDatabase\Th isIsA_BigDatabase.mdb

[MMcCarthy]

@Denburt
Useful information, indeed :)

[Denburt]

Here is another FYI...

http://office.microsoft.com/en-us/access/hp051874531033.aspx
"You can greatly enhance performance, when opening the main database and opening tables and forms, by forcing the linked database to remain open. To do this, create an empty table in the linked database, and link the table in the main database. Then, use the OpenRecordset method to open the linked table. This prevents the Microsoft Jet database engine from repeatedly opening and closing the linked database and from creating and deleting the associated .ldb file"

[MMcCarthy]

@Denburt
This is interesting.

It hasn't been my experience that the database opening and closing has caused any problems (i.e. the *ldb file) but I have to say I haven't monitored it closely.

What affect does opening the recordset have, if any, on multiple front end users? Also I assume that the variable to hold this recordset has to be globally set and retain it's value (as in this variable cannot be reused) until the frontend closes.

Mary

[Denburt]

Interesting points, to be honest I haven't sat down and timed it or noticed a hit when multiple users are using it, but I do open a recordset when the Main Menu opens I use it for version updates. I use the timer of the main menu to compare the linked table of the recordset to a local table periodically to see if a newer version is available. I tend to update the front end quite frequently. When the Main menu closes it closes that linked recordset.

[NeoPa]

A new question was posted in here. I've moved it to its own thread (How do I Use ReLink()) as posting questions within an article thread is not allowed, nor is it sensible. Who'd ever see it to respond to it after all?