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.
In this tutorial:
- Creating an Automated Build
- Design the Form
- Retrieving Information from the Source Database
- Building the Application
- Creating the Target Database
- Set Build Properties on the Target Database
- Setting Build Properties on the Source Database
- Deleting Data from Tables
- Calculating the Version Number
- Handling Application Dependencies
- Updating References
- Testing Reference Fix-Up
- Late Binding
- Licensing Your Applications
- Number of Records
- Restricting the Number of Times an Application is Launched
- Registering an Application
- Creating a Registration Web Service
- Validate the Registration Using the Web Service
- Miscellaneous Deployment Scenarios
- Create the Client Application
- Testing the Versioning Server
- Re-Linking Tables Automatically
- Programmatically Creating DSNs
- Creating a User DSN
- Ensuring an Application Runs Locally