MS-Access / Getting Started

---

Re-Linking Tables Automatically

Many Access applications split the database - that is, the tables are located in one database while the application components (forms, reports, and code) are in another. The database where the tables reside is called the back-end database, and as you'd imagine, the other database is called the front-end. Tables in the front-end are linked to the back-end. To split the database in Access 2007, choose the Database Tools tab in the Ribbon and then click the Access Database button in the Move Data group. This opens the Database Splitter.

By default, the Database Splitter creates a new database with the text _be added to the name of your database.

When deploying a solution where the database is split, you may need code that dynamically refreshes the linked tables in the front-end database. This is to make sure that the database always has the latest connection information. To do this, create a new module and add the following routine:

Public Function EnsureTablesConnected() As Boolean
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim stBackendDb As String
    Dim stNewBackend As String
    Dim stNotUpdated As String
    Dim iUpdated As Integer ' number of tables updated

    ' get the current database
    Set db = CurrentDb()

The first thing we want to do is iterate through all the tables in the database so we'll begin a loop. We're only concerned with linked tables so we'll check the length of the Connect property of the TableDef object in DAO to determine if the table is linked.

    ' iterate through the tables and refresh links
    For Each td In db.TableDefs
	If (Len(td.Connect) > 0) Then

The Connect property of the TableDef stores the connection string. For a table linked to another Access database, this is in the form: ;DATABASE=<PathToDatabase>. Therefore, we'll want to remove the prefix from the connection string to get the path to the database itself.

	    ' get the backend database for the table
	    stBackendDb = Mid(td.Connect, Len(";DATABASE=") + 1)

If this file doesn't exist, prompt for a new one. We'll implement the GetFileName called here in a moment.

	    ' if the file doesn't exist, then prompt
	    If (stNewBackend = "" And Dir(stBackendDb) = "") Then
		stNewBackend = GetFileName()
	    End If

If the user selected a new back-end database (as specified in stNewBackend), then update the Connect property of the table:

	    If (Len(stNewBackend) > 0) Then
		' refresh
		td.Connect = ";DATABASE=" & stNewBackend

Next, we need to refresh the linked table using the RefreshLink method. If this fails for any reason, we're appending the name of the offending table to a variable called stNotUpdated. We'll use this to display an error to the user later on.

		' do some inline error handling in case something fails
		On Error Resume Next

		td.RefreshLink

		If (Err <> 0) Then
		    stNotUpdated = stNotUpdated & td.Name & vbCrLf
		    Err.Clear
		End If
		On Error GoTo 0

We declared a counter variable to indicate how many tables were updated. Increment the counter and close the If blocks and For loop:

		' update the counter
		iUpdated = iUpdated + 1
	    End If
	End If
    Next

If any tables were not updated, we want to display them to the user; otherwise, display a message that the update succeeded. The iUpdated counter variable is used to display the succeeded message only when tables were actually updated.

    ' check for errors
    If (Len(stNotUpdated) > 0) Then
	MsgBox "Tables not updated: " & vbCrLf & _
	    Trim(stNotUpdated), vbExclamation, "Relink Tables Complete"
    Else
	If (iUpdated > 0) Then
	    MsgBox "Tables relinked successfully!", vbInformation, "Relink Tables
    Complete"
	End If
    End If

Last, do some cleanup and exit the function:

    ' cleanup
    db.Close
    Set td = Nothing
    Set db = Nothing
End Function

The GetFileName function is used here to display the standard Open file dialog box to the user. This function is implemented as follows. You'll need a reference to the Microsoft Office 12.0 Object Library for this code to work.

Public Function GetFileName() As String
    With Application.FileDialog(msoFileDialogFilePicker)
	.Title = "Select backend database"
	.Show
	If (.SelectedItems.Count > 0) Then
	    GetFileName = .SelectedItems(1)
	End If
    End With
End Function

To test this, you need a database with some linked tables. To get the database to verify that the tables are linked when the application loads, call this routine using the RunCode action in a macro named autoexec.

The RunCode action will fail if the database is opened in disabled mode.