ACC: How to Relink Back-End Tables with Common Dialog Control

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article contains an example that shows you how to refresh linked tables in your database using the Common Dialog control that is available with Microsoft Office 97 Developer Edition Tools or the Microsoft Access Developer's Toolkit version 7.0.

For an example of a different method to relink tables that uses Windows application programming interface (API) functions, refer to the Developer Solutions sample application (Solutions.mdb) included with Microsoft Access 7.0 and 97. Open the Solutions.mdb database, select "Use multiple databases" in the "Select a Category of Examples" box, and then "Link tables at startup" in the Select An Example box.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

An application that uses split database design has its tables in one database in a shared network location (the back-end database), and all its queries, forms, reports, macros and modules in another database on each client computer (the front-end database). The front-end database links all of its tables to the back-end database. If the back-end database is moved, errors occur in your application. You can build functionality into your application to detect that the back-end database file is missing from its expected location, and then prompt the user for the new location.

The following example demonstrates a method for relinking table data in your application. This method creates a form that a user can open to relink the back-end tables, and optionally uses a form to automatically verify the back-end link behind the scenes.

This article assumes that you have the Microsoft Common Dialog control, which is available with the Microsoft Office 97 Developer Edition and the Microsoft Access Developer's Toolkit version 7.0. If you do not have this control, skip the Sub procedure cmdBrowse_Click() in the example.

NOTE: If you use the Common Dialog control and you plan to distribute your database application, you must include the Common Dialog Control file, Comdlg32.ocx, and its supporting DLLs with your setup files. For more information about which supporting files to include with ActiveX controls in Microsoft Office 97 Developer Edition applications, search the Help Index for "ActiveX controls, files required for." For more information about which supporting files to include with OLE Custom Controls in Microsoft Access Developer's Toolkit version 7.0 applications, click the Help button on the "Add the files that you want your custom Setup program to copy" screen of the Setup Wizard.

Method to Relink Back-End Database Tables

1. Create a new blank database called FrontEnd.mdb.

2. On the File menu, point to Get External Data, and then click Link Tables. Create a link to each of the tables in the sample database Northwind.mdb.

3. Create a new form not based on any table or query in Design view:

   Form: frmNewDataFile -------------------------

   Text box:

             Name: txtFileName
             Width: 3"
          Common Dialog control:
             Name: xDialog
          Command button:
             Name: cmdBrowse
             Caption: Browse...
          Command button:
             Name: cmdLinkNew
             Caption: Refresh Links
          Command button:
             Name: cmdCancel
             Caption: Cancel
             Cancel: Yes
   
   

4. On the View menu, click Code.

5. Type the following procedures:

   '****************************************************************** ' The TablesMatch function assumes that strFileName exists and is a ' Microsoft Access database. ' Checks table names in strFileName to ensure they have a matching ' link in the current database. '****************************************************************** Private Function TablesMatch(strFileName As String) As Integer On Error GoTo Err_TablesMatch Dim intFlag As Integer, dbLocal As Database, dbBackEnd As Database Dim tdLocal As TableDef, tdBackEnd As TableDef

         intFlag = False   ' Initial value, process by exception.
   
         Set dbLocal = CurrentDb
         Set dbBackEnd = DBEngine(0).OpenDatabase(strFileName)
         For Each tdLocal In dbLocal.TableDefs   ' Loop through local tables.
            If Len(tdLocal.Connect) > 0 Then  ' This is an attached table.
                For Each tdBackEnd In dbBackEnd.TableDefs ' Loop thru back-end
                     If tdLocal.SourceTableName = tdBackEnd.Name Then
                        intFlag = True  ' We found a match in back-end.
                        Exit For   ' Jump out for next search.
                     End If
                Next
                If intFlag = False Then ' No match for this attached table.
                    TablesMatch = False
                    Exit Function
                Else ' There was a match--continue searching next attachment.
                    intFlag = False ' Reset flag for next table test.
                End If
            End If
         Next
         ' If the function reached here, all tables matched.
         TablesMatch = True
         Exit_TablesMatch:
         Exit Function
         Err_TablesMatch:
         MsgBox "The file isn't an MS Access Database or may be corrupted."
         TablesMatch = False
         Resume Exit_TablesMatch
         End Function
   
         Private Sub cmdLinkNew_Click()
         On Error GoTo Err_cmdLinkNew_Click
         Dim strTest As String, dbLocal As Database
         Dim tdLocal As TableDef
         On Error Resume Next   ' Turn off error check for test.
         strTest = Dir(Me![txtFileName])
         On Error GoTo Err_cmdLinkNew_Click
         If Len(strTest) = 0 Then   ' File not found.
            MsgBox "File not found. Please try again.", vbExclamation, _
            "Link to new data file"
         ElseIf TablesMatch(Me![txtFileName]) Then   ' A valid database.
            Set dbLocal = CurrentDb
            DoCmd.Hourglass True
            For Each tdLocal In dbLocal.TableDefs   ' Loop through all tables.
               If Len(tdLocal.Connect) > 0 Then   ' This is an linked table.
                  DoCmd.Echo True, "Linking " & tdLocal.Name
                  tdLocal.Connect = ";DATABASE=" & Trim(Me![txtFileName])
                  tdLocal.RefreshLink   ' Commit table link to new location.
               End If
            Next
            DoCmd.Echo True, "Done"
            DoCmd.Hourglass False
            MsgBox "Linking to new back-end data file was successful."
            DoCmd.Close acForm, Me.Name   ' Close the form.
         Else   ' Tables didn't match the tablenames in this database.
            MsgBox "The tables in data file " & Me![txtFileName] & _
            " didn't match the current database"
         End If
         Exit_cmdLinkNew_Click:
         DoCmd.Echo True   ' Just in case of error jump.
         DoCmd.Hourglass False
         Exit Sub
         Err_cmdLinkNew_Click:
         MsgBox Err.Description
         Resume Exit_cmdLinkNew_Click
         End Sub
   
         Private Sub cmdCancel_Click()
         On Error GoTo Err_cmdCancel_Click
         MsgBox "Link to new back-end cancelled", vbExclamation, _
         "Cancel Refresh Link"   ' Give a warning of cancellation.
         DoCmd.Close acForm, Me.Name   ' Close the form.
         Exit_cmdCancel_Click:
         Exit Sub
         Err_cmdCancel_Click:
         MsgBox Err.Description
         Resume Exit_cmdCancel_Click
         End Sub
   
         Private Sub cmdBrowse_Click()
         ' Prompts user for back-end database file name.
         On Error GoTo Err_cmdBrowse_Click
         Dim strFileName As String
         Dim oDialog As Object
         Set oDialog = Me!xDialog.Object
         With oDialog   ' Ask for new file location.
            .DialogTitle = "Please Select New Data File"
            .Filter = "Access Database(*.mdb;*.mda;*.mde;*.mdw)|" & _
            "*.mdb; *.mda; *.mde; *.mdw|All(*.*)|*.*"
            .FilterIndex = 1
            .ShowOpen
            ' If user responded, put selection into textbox on form.
            If Len(.FileName) > 0 Then Me![txtFileName] = .FileName
         End With
         Exit_cmdBrowse_Click:
         Exit Sub
         Err_cmdBrowse_Click:
         MsgBox Err.Description
         Resume Exit_cmdBrowse_Click
         End Sub
   
   

6. On the Debug menu, click "Compile and Save All Modules" (in Microsoft Access 7.0, on the Run menu click "Compile All Modules"; then on the File menu, click "Save All Modules").

7. Save the frmNewDataFile form and close it.

8. Move the Northwind.mdb sample database to another folder on your hard drive so the linked tables in FrontEnd.mdb will need to be refreshed.

9. Open the frmNewDataFile form, and click the Browse button.

10. In the "Please Select New Data File" dialog box, locate Northwind.mdb in its new folder, and then click Open. Note that the path and file name of the database appears in the text box on your form.

11. Click the Refresh Links button on the form. Note that each table name displays on the status line as the link is refreshed, and that you receive the following message when the procedure is done:

    Linking to new back-end data file was successful.

Creating a Startup Form to Check Linked Tables

If you want to verify the linked tables automatically each time you open the FrontEnd.mdb database, you can follow these steps to create a hidden form for that purpose:

1. Create a new form not based on any table or query in Design view.

2. On the View menu, click Code.

3. Type the following procedure to execute when the form opens:

         Private Sub Form_Open(Cancel As Integer)
         ' Tests a linked table for valid back-end.
         On Error GoTo Err_Form_Open
         Dim strTest As String, db As Database
         Dim td As TableDef
         Set db = CurrentDb
         For Each td In db.TableDefs
            If Len(td.Connect) > 0 Then   ' Is a linked table.
               On Error Resume Next   ' Turn off error trap.
               strTest = Dir(Mid(td.Connect, 11))   ' Check file name.
               On Error GoTo err_Form_Open   ' Turn on error trap.
               If Len(strTest) = 0 Then   ' No matching file.
                  If MsgBox("Couldn't find the back-end file " & _
                     Mid(td.Connect, 11) & ". Please choose new data file.", _
                     vbExclamation + vbOKCancel + vbDefaultButton1, _
                     "Can't find backend data file.") = vbOK Then
                        DoCmd.OpenForm "frmNewDataFile"   ' Open prompt form.
                  Else
                     MsgBox "The linked tables can't find their source. " & _
                     "Please log onto network and restart the application."
                  End If
            End If
            DoCmd.Close acForm, Me.Name
            Exit For   ' Stop at one linked table.
         End If
         Next   ' Loop to next tabledef.
         Exit_Form_Open:
         Exit Sub
         Err_Form_Open:
         MsgBox "Oops!  " & Error.Description
         Resume Exit_Form_Open
         End Sub
   
   

4. On the Debug menu, click "Compile and Save All Modules" (in Microsoft Access 7.0, on the Run menu click "Compile All Modules"; then on the File menu, click "Save All Modules").

5. Save the form as frmCheckLink, and then close it.

6. Set frmCheckLink as your Startup form by clicking Startup on the Tools menu. In the Startup dialog box, select frmCheckLink in the Display Form box, and then click OK.

7. Make frmCheckLink a hidden form by using the right mouse button (right-click) to click frmCheckLink in the Database window, and then click Properties on the shortcut menu that appears. Click the Hidden check box in the frmCheckLink Properties dialog box, and then click OK.

8. Move the Northwind.mdb sample database to another folder on your hard drive so the linked tables in FrontEnd.mdb will need to be refreshed.

9. Close and then reopen FrontEnd.mdb. Note that you receive the following message:

         Couldn't find the back-end file <Database Name>. Please choose new
         data file.
   

   If you click OK, the frmNewDataFile form opens for you to select a new back-end database, and then refreshes your table links. If you click Cancel, you receive this message:

         The linked tables can't find their source. Please log onto network
         and restart the application.
   

REFERENCES

For information about customizing the example for refreshing linked tables in the Developer Solutions sample application (Solution.mdb), please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q154397
   TITLE     : ADT/ODE How to Modify RefreshTableLinks Module in
               Solutions.mdb