ADO Relinking Tables automatically and Deployment issues

[JeanineScott]

Couple of questions:

1) Does anyone have the code using ADO to relink tables autmatically upon app open?

2) Anyone know the MSDN CD that the Developer's Toolkit for 2000 is on? I've looked all over and can't find where it's supposed to be! The last one I've tried was #0743 but after I ran the install I still didn't have the deployment addin.

Thanks!!

 

[Guest_imported]

This white paper is no longer found in MSDN site. It is from MSDN Library CD. Good luck.

MOD2000: How to Refresh Links in a Run-Time Application

Q291264


--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Office 2000 Developer
Microsoft Access 2000

--------------------------------------------------------------------------------


SUMMARY
In a run-time application, the front-end and back-end databases must be stored in the same folder. Even though you can change the default installation path of the back-end database to, for example, $AppPath\Folder1, there is no way for Access to refresh the links in the front-end to match the new installation location.

One way to work around this behavior is to programmatically refresh the links by using ActiveX Extensibility Objects (ADOX) after the run-time application has been installed. The following is an illustration of how to implement this technique. Please note that this article assumes that the back-end databases are stored in subfolders under the front-end database.



MORE INFORMATION

Make sure you have the latest version of the Microsoft Data Access Components (MDAC) installed on your computer. MDAC can be found at the following Microsoft Web site:

http://www.microsoft.com/data

Set a reference to Microsoft ADO Ext 2.5 (or later) for DDL and Security.


Create a new database and name it FrontEnd.mdb.


Import the Customers and Employees forms from the sample database Northwind.mdb into FrontEnd.mdb.


Create a new database and subfolder under the folder where the FrontEnd.mdb file is located, for example, Folder1\Backend1.mdb.


Import the Customer table from Northwind.mdb into Backend1.mdb.


Create a new database and subfolder under the folder where the FrontEnd.mdb file is located, for example, Folder2\Backend2.mdb.


Import the Employee table from Northwind.mdb into Backend2.mdb.


Open the FrontEnd.mdb database and create a new module. In the module, paste in the following code:



Option Compare Database
Option Explicit

Private Declare Function apiSearchTreeForFile Lib "ImageHlp.dll" Alias _
"SearchTreeForFile" (ByVal lpRoot As String, ByVal lpInPath _
As String, ByVal lpOutPath As String) As Long


Function RefreshLinks()
On Error GoTo ErrorHandler


Dim objCat As New ADOX.Catalog 'Define the ADOX Catalog Object
Dim objTbl As ADOX.Table 'Define the ADOX Table Object

Dim strSearchFolder As String 'Folder to Search in.
Dim strFilename As String 'Db Name of the Linked Table
Dim strFullName As String 'Path & DB Name of the Linked Table.
Dim strSearchFile As String 'The new path of the database.

Dim blnTablesNotLinked As Boolean 'Determines if links are valid

'Open the catalog
objCat.ActiveConnection = CurrentProject.Connection

'Loop through the table collection and refresh the linked tables.
For Each objTbl In objCat.Tables

' Check to make sure the table is a linked table.
If objTbl.Type = "LINK" Then
strFullName = objTbl.Properties("Jet OLEDB:Link Datasource&quot
strFilename = Mid(strFullName, InStrRev(strFullName, "\", _
Len(strFullName)) + 1, Len(strFullName))
strSearchFolder = CurrentProject.Path
'The following line of code attempts to refresh the link.
'If the source cannot be found an error is generated.
'Please note that this code only checks one table to determine
'whether or not the links are valid.
objTbl.Properties("Jet OLEDB:Link Datasource&quot = strFullName

If blnTablesNotLinked = False Then
Exit Function
Else
'Set the search path to the path of the current project.
'The assumption is that the linked tables are located in subfolders.
strSearchFile = SearchFile(strFilename, strSearchFolder)
objTbl.Properties("Jet OLEDB:Link Datasource&quot = strSearchFile
End If
End If
Next

MsgBox "The links were successfully refreshed!!! "

ExitHandler:
Exit Function

ErrorHandler:
Select Case Err.Number
Case -2147467259
blnTablesNotLinked = True
Resume Next
Case Else
MsgBox Err.Description & " " & Err.Number
Resume ExitHandler
End Select
End Function

Function SearchFile(ByVal strFilename As String, _
ByVal strSearchPath As String) As String
'Search the folder for first occurence of the source databases.
Dim strBuffer As String
Dim lngResult As Long
SearchFile = ""
strBuffer = String$(1024, 0)
lngResult = apiSearchTreeForFile(strSearchPath, strFilename, strBuffer)
If lngResult <> 0 Then
If InStr(strBuffer, vbNullChar) > 0 Then
SearchFile = Left$(strBuffer, InStr(strBuffer, vbNullChar) - 1)
End If
End If
End Function
This function checks the first linked table in the database to determine if the links are valid. If the links are not valid, the function searches for the database and refreshes the links.
Create a new macro with the following properties, and name it AutoExec:



Action: RunCode
Function Name: RefreshLinks()

Action: OpenForm
Form Name: Customers

Action: OpenForm
Form Name: Employees
Note that you can also call this function in the Open event of a hidden Startup Form.
Build the package for the run-time application. Remember to add $(AppPath)\Folder1\ and $(AppPath)\Folder2\ to the respective installation locations of the databases on the Install Location page.


Deploy the run-time application.


When the application starts, an hourglass indicates that the links are being refreshed. The Customers and Employees forms will then open with valid data.

 

[JeanineScott]

Thank you so much for the example!