ACC: How to Relink Back-End Tables with Common Dialog ControlID: Q181076
|
Advanced: Requires expert coding, interoperability, and multiuser skills.
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
the 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 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.
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 Tools and
the Microsoft Access Developer's Toolkit version 7.0. 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 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 Tools 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.
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
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
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 Linking to new back-end data file was successful.
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 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.
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:
Q154397 ADT/ODE How to Modify RefreshTableLinks Module in Solutions.mdb
Additional query words: inf relink reattach reconnect refresh link attach connect ADT ODE
Keywords : kbdta PgmHowto
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto Last Reviewed: July 8, 1999