By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,472 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Relinking ODBC Tables using VBA

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Function relinkTables()
  2. Dim tdf As DAO.TableDef
  3.  
  4.     For Each tdf In CurrentDb.TableDefs
  5.         ' check if table is a linked table
  6.         If Len(tdf.Connect) > 0 Then
  7.             tdf.Connect = "odbc connection string to the DSN or database"
  8.             tdf.RefreshLink
  9.         End If
  10.     Next
  11.  
  12. End Function
  13.  
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
Nov 13 '09 #1
Share this Article
Share on Google+
8 Comments


NeoPa
Expert Mod 15k+
P: 31,186
I have one that works specifically for Access database linked tables. It may be worth including that and making it less specific :
Expand|Select|Wrap|Line Numbers
  1. 'ReLink() Updates links of all tables that currently link to strDBName to point
  2. 'to strDBName in the strFolder folder (if specified, otherwise the same folder
  3. 'as the current database).
  4. Public Sub ReLink(ByVal strDBName As String, _
  5.                   Optional ByVal strFolder As String = "")
  6.     Dim intParam As Integer, intErrNo As Integer
  7.     Dim strOldLink As String, strOldName As String
  8.     Dim strNewLink As String, strMsg As String
  9.     Dim varLinkAry As Variant
  10.     Dim db As DAO.Database
  11.     Dim tdf As DAO.TableDef
  12.  
  13.     Set db = CurrentDb()
  14.     If strFolder = "" Then strFolder = CurrentProject.Path
  15.     If Right(strFolder, 1) = "\" Then _
  16.         strFolder = Left(strFolder, Len(strFolder) - 1)
  17.     strNewLink = strFolder & "\" & strDBName
  18.     For Each tdf In db.TableDefs
  19.         With tdf
  20.             If .Attributes And dbAttachedTable Then
  21.                 varLinkAry = Split(.Connect, ";")
  22.                 For intParam = LBound(varLinkAry) To UBound(varLinkAry)
  23.                     If Left(varLinkAry(intParam), 9) = "DATABASE=" Then Exit For
  24.                 Next intParam
  25.                 strOldLink = Mid(varLinkAry(intParam), 10)
  26.                 If strOldLink <> strNewLink Then
  27.                     strOldName = Split(strOldLink, _
  28.                                        "\")(UBound(Split(strOldLink, "\")))
  29.                     If strOldName = strDBName Then
  30.                         varLinkAry(intParam) = "DATABASE=" & strNewLink
  31.                         .Connect = Join(varLinkAry, ";")
  32.                         On Error Resume Next
  33.                         Call .RefreshLink
  34.                         intErrNo = Err.Number
  35.                         On Error GoTo 0
  36.                         Select Case intErrNo
  37.                         Case 3011, 3024, 3044, 3055, 7874
  38.                             varLinkAry(intParam) = "DATABASE=" & strOldLink
  39.                             .Connect = Join(varLinkAry, ";")
  40.                             strMsg = "Database file (%F) not found.%L" & _
  41.                                      "Unable to ReLink [%T]."
  42.                             strMsg = Replace(strMsg, "%F", strNewLink)
  43.                             strMsg = Replace(strMsg, "%L", vbCrLf)
  44.                             strMsg = Replace(strMsg, "%T", .Name)
  45.                             Call MsgBox(Prompt:=strMsg, _
  46.                                         Buttons:=vbExclamation Or vbOKOnly, _
  47.                                         Title:="ReLink")
  48.                             If intErrNo = 3024 _
  49.                             Or intErrNo = 3044 _
  50.                             Or intErrNo = 3055 Then Exit For
  51.                         Case Else
  52.                             strMsg = "[%T] relinked to ""%F"""
  53.                             strMsg = Replace(strMsg, "%T", .Name)
  54.                             strMsg = Replace(strMsg, "%F", strNewLink)
  55.                             Debug.Print strMsg
  56.                         End Select
  57.                     End If
  58.                 End If
  59.             End If
  60.         End With
  61.     Next tdf
  62. 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.
Nov 13 '09 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Nice one Ade, valuable addition

Mary
Nov 14 '09 #3

Denburt
Expert 100+
P: 1,356
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.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3. 'Dim BkEnd As String
  4. Declare Function GetShortPathName Lib "kernel32" _
  5.             Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
  6.            ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long
  7. Function GetShortName(ByVal sLongFileName As String) As String
  8.            Dim lRetVal As Long, sShortPathName As String, iLen As Integer
  9.            'Set up a buffer area for the API function call return.
  10.            sShortPathName = Space(255)
  11.            iLen = Len(sShortPathName)
  12.  
  13.            'Call the function.
  14.            lRetVal = GetShortPathName(sLongFileName, sShortPathName, iLen)
  15.            'Remove unwanted characters.
  16.            GetShortName = Left(sShortPathName, lRetVal)
  17.  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
Nov 19 '09 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
@Denburt
Useful information, indeed :)
Nov 19 '09 #5

Denburt
Expert 100+
P: 1,356
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"
Nov 19 '09 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
@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
Nov 19 '09 #7

Denburt
Expert 100+
P: 1,356
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.
Nov 20 '09 #8

NeoPa
Expert Mod 15k+
P: 31,186
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?
Apr 25 '18 #9