How to relink back-end tables with the common dialog control in Access 2000 (209862)



The information in this article applies to:

  • Microsoft Access 2000

This article was previously published under Q209862
For a Microsoft Access 97 version of this article, see 181076.
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

This article shows you how to use the Common Dialog control to refresh linked tables in your database. The Common Dialog control is available with Microsoft Office 2000 Developer Edition Tools. 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 Microsoft Windows application programming interface (API) functions, refer to the Developer Solutions sample program (Solutions9.mdb.) To obtain Solutions9.mdb, click the article number below to view the article in the Microsoft Knowledge Base:

248674 ACC2000: Orders and Developer Solutions Sample Databases Available on the Microsoft Developer Network (MSDN)

Open the Solutions9.mdb database, select Use multiple databases in the Select a Category of Examples box, and then click Link tables at startup in the Select an Example box.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. A program that uses split database design has its tables in one database in a shared network location (the back-end database), and all of 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 program. You can enable your program 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 shows you how to relink table data in your program. This method uses a form that a user can open to relink the back-end tables, and another optional form that can automatically verify the back-end link when the database is opened.

This article assumes that you have the Microsoft Common Dialog control, which is available with Microsoft Office 2000 Developer Edition Tools. If you do not have this control, skip the Browse() function in the example.

NOTE: If you use the Common Dialog control and you plan to distribute your database program, you must use the Package and Deployment Wizard to include the Common Dialog Control file, Comdlg32.ocx, and its supporting DLLs with your setup files. For more information about using the Package and Deployment Wizard, see the articles listed in the "References" section of this article.

Method to Relink Back-End Database Tables

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Create a new blank Access database file and name it FrontEnd.mdb.
  2. On the File menu, point to Get External Data, and then click Link Tables.
  3. Browse to select the sample database Northwind.mdb, and then click Link. In the Link Tables dialog box, click Select All, and then click OK.
  4. Create the following 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...
    On Click: =Browse()

    Command button:

    Name: cmdLinkNew
    Caption: Refresh Links
    On Click: =Processtables()

    Command button:

    Name: cmdCancel
    Caption: Cancel
    OnClick: [Event Procedure]
    Cancel: Yes

  5. On the View menu, click Code.
  6. 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"
       DoCmd.Close acForm, Me.Name
    
    Exit_cmdCancel_Click:
       Exit Sub
    
    Err_cmdCancel_Click:
       MsgBox Err.Description
       Resume Exit_cmdCancel_Click
    
    End Sub
    					
  7. On the Debug menu, click Compile FrontEnd.
  8. Save the frmNewDataFile form and close it.
  9. In the Database window, click Modules, and then click New.
  10. On the Tools menu, click References, select Microsoft DAO 3.6 Object Library if it is not already selected, and then click OK.
  11. Type or paste the following code:
    Dim UnProcessed As New Collection
           
    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 Len(.FileName) > 0 Then  ' user responded, put selection into text box on form.
               [Forms]![frmNewDatafile]![txtFileName] = .FileName
            End If
        End With
    
    Exit_Browse:
        Exit Function
    
    Err_Browse:
        MsgBox Err.Description
        Resume Exit_Browse
    
    End Function
    
    Public Sub AppendTables()
    
        Dim db As DAO.Database, x As Variant
        Dim strTest As String
        ' Add names of all table with invalid links to the Unprocessed Collection.
        Set db = CurrentDb
        ClearAll
        For Each x In db.TableDefs
            If Len(x.Connect) > 1 And Len(Dir(Mid(x.Connect, 11))) = 0 Then
            ' connect string exists, but file does not
                 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 tables with broken links 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.Number & ": " & 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 DAO.Database, dblocal As DAO.Database, ws As Workspace, x, y
        Dim tdlocal As DAO.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.Number & ": " & 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.Number & ": " & Err.Description
        Resume Exit_BeginLink
    
    End Sub
    					
  12. On the Debug menu, click Compile FrontEnd.
  13. Save the module as RelinkCode, and then close it.
  14. Rename the Northwind.mdb sample database or move it to another folder on your hard disk so that the linked tables in FrontEnd.mdb will need to be refreshed.
  15. Open the frmNewDataFile form, and then click the Browse button.
  16. 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.
  17. Click the Refresh Links button on the form.

    Note that you receive the following message when the procedure is finished:

    Linking to new back-end data file was successful.

Creating a Startup Form to Check Linked Tables

To verify the links to back-end tables automatically each time that you open the FrontEnd.mdb database, you can create a hidden form for that purpose by following these steps:
  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 run 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 DAO.Database
          Dim td As DAO.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.
                         DoCmd.Close acForm, Me.Name
                         Exit Sub                          ' to refresh links
                   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 Err.Number & ": " & Error.Description
          Resume Exit_Form_Open
          End Sub
    					
  4. On the Debug menu, click Compile FrontEnd.
  5. Save the form as frmCheckLink, and then close it.
  6. To set frmCheckLink as the startup form, on the Tools menu click Startup, select frmCheckLink in the Display Form/Page list, and then click OK.
  7. To make frmCheckLink a hidden form, right-click frmCheckLink in the Database window, click Properties on the shortcut menu, click to select 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 disk so that the linked tables in FrontEnd.mdb will need to be refreshed.
  9. Close and then reopen FrontEnd.mdb.

    Notice that you receive the following error message
    Can't find the back-end file database file. Please choose new data file.
    where database file is the path and file name of your back-end database.

    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 the following error message:
    The linked tables can't find their source. Please log onto network and restart the program.

REFERENCES

For additional information about customizing the example for refreshing linked tables in the Developer Solutions sample program (Solutions9.mdb), click the article number below to view the article in the Microsoft Knowledge Base:

154397 ADT/ODE How to Modify RefreshTableLinks Module in Solutions.mdb

For additional information about the Package and Deployment Wizard, click the article numbers below to view the articles in the Microsoft Knowledge Base:

240956 MOD2000: Package and Deployment Wizard Now Used to Create a Distributable Application

236143 MOD2000: How to Start the Package and Deployment Wizard


Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbProgramming kberrmsg kbhowto kbinfo KB209862