Skip to main content
Microsoft 365
Subscribe
4 min read

Automatically relink Microsoft Access tables

By

Relinking Tables in Access(Guest blogger Patrick Wood, an Access MVP, is the founder of Gaining Access Technologies, a software company specializing in working with SQL Azure and Access. He also founded Microsoft Access and SQL Azure Information Center for developers. For more information, contact patwood@gainingaccess.net)

In this article we will show you how you can call a single procedure using just a little code to automatically relink your back-end tables, even when the database files have been moved to a different folder. The procedure will also relink the tables in multiple back-ends. And best of all, you don’t have to know much about writing code to make this work.

Automating the relinking of tables just makes good sense. This is especially true if you are distributing your database to others. It can save you a lot of headaches, trouble, travel, and time. You do not have to be physically present, nor do you need to remotely access another computer. Additionally, you do not have to enter the names of your back end database(s) or your linked tables.

How does it work?

The code gets important information from the TableDefs collection in your front-end. This includes the names of your linked tables and the names of your back-end databases. The code then uses this information along with the existing path of the front-end database to get the string needed to set the TableDef Connect Property. The code loops through the TableDefs Collection and uses the TableDef RefreshLink Method to Refresh the links.

Here is what the code will do:

  • Relink all linked Access database tables if the back-end and front-end are in the same folder.
  • Relink tables when the databases are moved to a different folder or a different computer.
  • Relink tables in multiple back-end Access databases.
  • Does not require you enter or store database or table names.
  • Unlike some methods, will not lose your linked table or TableDef if there is an error.

There are a few things the code will not do:

  • It will not relink Access database tables unless they have already been linked.
  • It will not refresh the links of tables that have been renamed or moved to other back-ends.
  • It will not relink tables that are in back-ends that have been renamed.
  • It will not relink ODBC, Excel, Outlook, dBASE, Paradox or any other type of linked table or object.

There are a couple of Requirements:

1) The front-end and back-end tables must be in the same folder.

Fulfilling this requirement allows the files to be moved to any folder, or a different computer, and the code will automatically relink the tables.

2) The code that calls the RefreshTableLinks procedure must be added to the Form Open Event of the first form to be loaded or called by an AutoExec macro.

To be more precise, the code must be run before a bound form loads data from the tables. So you could use other procedures in unbound forms, such as a Splash form, before calling the RefreshTableLinks procedure.

The function that relinks the tables:

‘—————————————————————————-
‘ Procedure: RefreshTableLinks
‘ Purpose: Refresh table links to back-ends in the same folder as front end.
‘ Note: Linked Tables can be in more than one back-end.
‘ Return: Returns a zero-length string if all tables are relinked.
‘ Return: Or returns a string listing tables not relinked and errors.
‘—————————————————————————-

Public Function RefreshTableLinks() As String
On Error GoTo ErrHandle

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strCon As String
Dim strBackEnd As String
Dim strMsg As String
Dim intErrorCount As Integer

Set db = CurrentDb

‘Loop through the TableDefs Collection.
For Each tdf In db.TableDefs
‘Verify the table is a linked table.
If Left$(tdf.Connect, 10) = “;DATABASE=” Then
‘Get the existing Connection String.
strCon = Nz(tdf.Connect, “”)
‘Get the name of the back-end database using String Functions.
strBackEnd = Right$(strCon, (Len(strCon) – (InStrRev(strCon, “”) – 1)))
‘Verify we have a value for the back-end
If Len(strBackEnd & “”) > 0 Then
‘Set a reference to the TableDef Object.
Set tdf = db.TableDefs(tdf.Name)
‘Build the new Connection Property Value.
tdf.Connect = “;DATABASE=” & CurrentProject.Path & strBackEnd
‘Refresh the table link.
tdf.RefreshLink
Else
‘There was a problem getting the name of the back-end.
‘Add the information to the message to notify the user.
intErrorCount = intErrorCount + 1
strMsg = strMsg & “Error getting back-end database name.” & vbNewLine
strMsg = strMsg & “Table Name: ” & tdf.Name & vbNewLine
strMsg = strMsg & “Connect = ” & strCon & vbNewLine
End If
End If
Next tdf

ExitHere:
On Error Resume Next
If intErrorCount > 0 Then
strMsg = “There were errors refreshing the table links: ” _
& vbNewLine & strMsg & “In Procedure RefreshTableLinks”
RefreshTableLinks = strMsg
End If
Set tdf = Nothing
Set db = Nothing
Exit Function

ErrHandle:
intErrorCount = intErrorCount + 1
strMsg = strMsg & “Error ” & Err.Number & ” ” & Err.Description
strMsg = strMsg & vbNewLine & “Table Name: ” & tdf.Name & vbNewLine
strMsg = strMsg & “Connect = ” & strCon & vbNewLine
Resume ExitHere

End Function

The above procedure only returns a message string if an error has occurred. This allows you to quietly record the successful relinking of tables in the Immediate Window instead of using an annoying MsgBox that pops up every time you open the database. However, you can use the MsgBox if need to notify the user when one or more of the tables fails to be relinked or there is an error.

The code below shows one way to call the procedure and use the MsgBox to display errors.

Dim strMsg As String

‘Run the Procedure, getting any error messages.
strMsg = RefreshTableLinks()

‘strMsg will be a zero-length string if there is no error message.
If Len(strMsg & “”) = 0 Then
Debug.Print “All Tables were successfully relinked.”
Else
‘Notify the user of the errors.
MsgBox strMsg, vbCritical
End If

It is a good idea to let the procedure run each time the database is opened. You can put the RefreshTableLinks Function in a Standard Module, add the code to call the procedure, and you are all done. You can forget about it – at least until there is a failure to relink a table. Barring corruption of a database, this should be a rare case; that is unless someone decides to move one of the files to a different folder or rename the back-end database.

This code will only connect to Access Database tables that have already been linked in the front-end database. You can learn more by reading about the DAO TableDef Connect Property for Access 2010. You can also learn more by reading about the DAO TableDef RefreshLink Method for Access 2010.

Related Posts