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

This article was previously published under Q181076
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you how to use the Common Dialog control to refreshlinked tables in your database. The Common Dialog control is available withthe Microsoft Office 97 Developer Edition Tools or the Microsoft Access Developer's Toolkit version 7.0. The tables being linked may reside in one or many back-end databases.

For an example of a different method to relink tables that uses Windowsapplication programming interface (API) functions, refer to the DeveloperSolutions sample application (Solutions.mdb) included with Microsoft Access7.0 and 97. Open the Solutions.mdb database, select "Use multipledatabases" in the "Select a Category of Examples" box, and then "Linktables at startup" in the Select An Example box.

This article assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout 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 onedatabase in a shared network location (the back-end database), and all itsqueries, forms, reports, macros, and modules in another database on eachclient computer (the front-end database). The front-end database links allof its tables to the back-end database. If the back-end database is moved,errors occur in your application. You can build functionality into yourapplication to detect that the back-end database file is missing from itsexpected location, and then prompt the user for the new location.

The following example demonstrates a method for relinking table data inyour application. This method creates a form that a user can open to relinkthe back-end tables, and optionally uses a form to automatically verify theback-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 Tools andthe Microsoft Access Developer's Toolkit version 7.0. If you do not havethis control, skip the Browse() function in the example.

NOTE: If you use the Common Dialog control and you plan to distribute yourdatabase application, you must include the Common Dialog Control file,Comdlg32.ocx, and its supporting DLLs with your setup files. For moreinformation about which supporting files to include with ActiveX controlsin Microsoft Office 97 Developer Edition Tools applications, search theHelp Index for "ActiveX controls, files required for." For more informationabout which supporting files to include with OLE Custom Controls inMicrosoft Access Developer's Toolkit version 7.0 applications, click theHelp button on the "Add the files that you want your custom Setup programto 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 the following new form not based on any table or query inDesign view:
           Form: frmNewDataFile       --------------------------       Text box:          Name: txtFileName          Width: 3"       Common Dialog control:          Name: xDialog       Command button:          Name: cmdBrowse          Caption: Browse...          OnClick: =Browse()       Command button:          Name: cmdLinkNew          Caption: Refresh Links          OnClick: =Processtables()       Command button:          Name: cmdCancel          Caption: Cancel          Cancel: Yes					
  4. On the View menu, click Code.
  5. Type or paste the following procedure:
           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					
  6. On the Debug menu, click "Compile and Save All Modules" (or 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. In the Database Window, click the Modules tab,
  9. Create a new module and type or paste the following code:
           Option Compare Database       Dim UnProcessed As New Collection       Option Explicit       Public Function Browse()       ' Prompts user for back-end database file name.       On Error GoTo Err_Browse       Dim strFilename As String       Dim oDialog As Object       Set oDialog = [Forms]![frmNewDatafile]!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 text box on form.         If Len(.FileName) > 0 Then _         [Forms]![frmNewDatafile]![txtFileName] = .FileName       End With       Exit_Browse:          Exit Function       Err_Browse:          MsgBox Err.Description          Resume Exit_Browse       End Function       Public Sub AppendTables()       Dim db As Database, x As Variant       ' Add all linked table names into the Unprocessed Collection.       Set db = CurrentDb       ClearAll       For Each x In db.TableDefs         If Len(x.Connect) > 1 Then              UnProcessed.Add Item:=x.Name, key:=x.Name          End If       Next       End Sub       Public Function ProcessTables()       Dim strTest As String       On Error GoTo Err_BeginLink       ' Call procedure to add all linked tables into a collection.       AppendTables       ' Test for existence of file name\directory selected in       ' Common Dialog Control.       strTest = Dir([Forms]![frmNewDatafile]![txtFileName])       On Error GoTo Err_BeginLink       If Len(strTest) = 0 Then   ' File not found.         MsgBox "File not found. Please try again.", vbExclamation, _         "Link to new data file"          Exit Function       End If       ' Begin relinking tables.       Relinktables (strTest)       ' Check to see if all tables have been relinked.       CheckifComplete       DoCmd.Echo True, "Done"         If UnProcessed.Count < 1 Then            MsgBox "Linking to new back-end data file was successful."         Else            MsgBox "Not All back-end tables were successfully relinked"         End If       DoCmd.Close acForm, [Forms]![frmNewDatafile].Name       Exit_BeginLink:          DoCmd.Echo True          Exit Function       Err_BeginLink:           Debug.Print Err.Number           If Err.Number = 457 Then               ClearAll               Resume Next           End If           MsgBox Err.Description           Resume Exit_BeginLink       End Function       Public Sub ClearAll()       Dim x       ' Clear any and all names from the Unprocessed Collection.       For Each x In UnProcessed         UnProcessed.Remove (x)       Next       End Sub       Public Function Relinktables(strFilename As String)       Dim dbbackend As Database, dblocal As Database, ws As Workspace, _           x, y       Dim tdlocal As TableDef       On Error GoTo Err_Relink       Set dbbackend = DBEngine(0).OpenDatabase(strFilename)       Set dblocal = CurrentDb       ' If the local linked table name is found in the back-end database       ' we're looking at, Recreate & Refresh its connect string, and       ' then remove its name from the Unprocessed collection.        For Each x In UnProcessed           If Len(dblocal.TableDefs(x).Connect) > 0 Then             For Each y In dbbackend.TableDefs                If y.Name = x Then                   Set tdlocal = dblocal.TableDefs(x)                   tdlocal.Connect = ";DATABASE=" & _                   Trim([Forms]![frmNewDatafile]![txtFileName])                   tdlocal.RefreshLink                   UnProcessed.Remove (x)                End If             Next           End If       Next       Exit_Relink:          Exit Function       Err_Relink:          MsgBox Err.Description          Resume Exit_Relink       End Function       Public Sub CheckifComplete()       Dim strTest As String, y As String, notfound As String, x       On Error GoTo Err_BeginLink       ' If there are any names left in the unprocessed collection,       ' then continue.       If UnProcessed.Count > 0 Then          For Each x In UnProcessed               notfound = notfound & x & Chr(13)          Next          ' List the tables that have not yet been relinked.          y = MsgBox("The following tables were not found in " & _          Chr(13) & Chr(13) & [Forms]![frmNewDatafile]!txtFileName _          & ":" & Chr(13) & Chr(13) & notfound & Chr(13) & _          "Select another database that contains the additional tables?", _          vbQuestion + vbYesNo, "Tables not found")          If y = vbNo Then              Exit Sub          End If          ' Bring the Common Dialog Control back up.          Browse          strTest = Dir([Forms]![frmNewDatafile]![txtFileName])          If Len(strTest) = 0 Then   ' File not found.               MsgBox "File not found. Please try again.", vbExclamation, _               "Link to new data file"               Exit Sub          End If          Debug.Print "Break"          Relinktables (strTest)       Else          Exit Sub       End If           CheckifComplete       Exit_BeginLink:          DoCmd.Echo True   ' Just in case of error jump.          DoCmd.Hourglass False          Exit Sub       Err_BeginLink:          Debug.Print Err.Number          If Err.Number = 457 Then             ClearAll             Resume Next          End If          MsgBox Err.Description          Resume Exit_BeginLink       End Sub					
  10. 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").
  11. Save the module as RelinkCode and close it.
  12. 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.
  13. Open the frmNewDataFile form, and click the Browse button.
  14. 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.
  15. Click the Refresh Links button on the form. Note 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 that youopen the FrontEnd.mdb database, you can follow these steps to create ahidden 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         End If      Next   ' Loop to next tabledef.      DoCmd.Close acForm, Me.Name      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" (or 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 newdata 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 tablesin the Developer Solutions sample application (Solution.mdb), please seethe following article in the Microsoft Knowledge Base:
154397ADT/ODE How to Modify RefreshTableLinks Module in Solutions.mdb
inf relink reattach reconnect refresh link attach connect ADT ODE
Properties

Article ID: 181076 - Last Review: 12/05/2015 08:23:36 - Revision: 3.3

Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbhowto kbprogramming KB181076
Feedback