Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Looking at Other Issues

Two additional issues have not yet been covered regarding the distribution of your application. The first involves ensuring that the application database can establish any links that it has to external tables. The second involves the prospect of using replication to effectively distribute changes to your application.

Automating the Process of Linking to Tables

Access hard-codes locations for table links. This means that, if you install your application on another machine, the tables will not link successfully unless the other machine has exactly the same folder structure as you do on your machine. The code shown in Listing 32.4 checks to see whether the required tables are available. If they are not found in the expected location, the routine attempts to locate them in the same folder that holds them in the application database. If they still cannot be found, the user is given an opportunity to locate the files. If they

still cannot be found, the application terminates.

Listing 32.4 The LinkTables Routine

Sub LinkTables() On Error GoTo LinkTables_Err: Dim objFileDialog As FileDialog Dim strFileName As String 'Determine if links are ok If Not VerifyLink Then 'If links not ok, attempt to link with default file name 'in the current folder If Not ReLink(CurrentProject.FullName, True) Then 'If still unsuccessful, allow user to locate the data database MsgBox "You Must Locate Tables to Proceed" & vbCrLf & _ "The Tables are Located in the Chap32Data Database" & vbCrLf & _ "in the Directory Where You Placed the Sample Files" Set objFileDialog = Application.FileDialog(msoFileDialogOpen) With objFileDialog .Show .AllowMultiSelect = False strFileName = .SelectedItems(1) End With 'Attempt to relink with the database the user selected If Not ReLink(strFileName, False) Then 'If still unsuccessful, display message to the user and 'return false from this routine MsgBox "You Cannot Run This App Without Locating Data Tables" LinkTables = False Else 'User successfully designated new location; return True LinkTables = True End If Else 'Data database located with default name in the same location 'as the application database; return True LinkTables = True End If Else 'Table links not broken; return True LinkTables = True End If Exit Function LinkTables_Err: MsgBox "Error # " & Err.Number & ": " & Err.Description Exit Function End Sub

The routine begins by executing a function called VerifyLink. The VerifyLink function is shown in Listing 32.5 and is found in the FinalLibrary.MDA file on the sample code CD.

Listing 32.5 The VerifyLink Function

Function VerifyLink() As Boolean 'Verify connection information in linked tables. 'Declare Required Variables Dim cat As ADOX.Catalog Dim tdf As ADOX.Table Dim strTemp As String 'Point Database object variable at the current database Set cat = New ADOX.Catalog With cat Set .ActiveConnection = CurrentProject.Connection 'Continue if links are broken. On Error Resume Next 'Open one linked table to see if connection 'information is correct. 'For Each tdf In .Tables ' If tdf.Type = "LINK" Then ' strTemp = tdf.Columns(0).Name ' If Err.Number Then ' Exit For ' End If ' End If 'Next tdf 'If code above is too slow, this is the 'less conservative alternative For Each tdf In .Tables If tdf.Type = "LINK" Then strTemp = tdf.Columns(0).Name Exit For End If Next tdf End With VerifyLink = (Err.Number = 0) End Function

The VerifyLink function first creates an ADOX Catalog object. It sets the ActiveConnection property of the Catalog object to the Connection property of the CurrentProject. The CurrentProject object returns a reference to the database using the library, rather than to the library itself. The heart of the routine is the For…Next loop. It loops through each Table object in the Tables collection of the Catalog object. If the table is linked, it attempts to reference the first field in the table. If an error occurs, the table link must be broken. The error number is nonzero, and the routine exits the For…Next loop. Because the function returns whether or not the error number is equal to zero, False is returned if an error occurs, and True is returned if no error occurs.

If the VerifyLink function returns a False, the Relink function is executed. The Relink function is shown in Listing 32.6.

Listing 32.6 The Relink Function

Function ReLink(strDir As String, DefaultData As Boolean) _ As Boolean Dim cat As ADOX.Catalog Dim tdfRelink As ADOX.Table Dim oDBInfo As DBInfo Dim strPath As String Dim strName As String Dim intCounter As Integer Dim vntStatus As Variant 'Update status bar vntStatus = SysCmd(acSysCmdSetStatus, "Updating Links") Set cat = New ADOX.Catalog Set oDBInfo = New DBInfo With cat 'Use File Information class to extract the application 'database file name .ActiveConnection = CurrentProject.Connection oDBInfo.FullName = strDir strPath = oDBInfo.FilePathOnly strName = Left(oDBInfo.FileName, InStr(oDBInfo.FileName, ".") - 1) 'Disable error handling On Error Resume Next 'Update progress meter Call SysCmd(acSysCmdInitMeter, "Linking Data Tables", .Tables.Count) 'Loop through each table, attempting to relink For Each tdfRelink In .Tables intCounter = intCounter + 1 Call SysCmd(acSysCmdUpdateMeter, intCounter) If .Tables(tdfRelink.Name).Type = "LINK" Then tdfRelink.Properties("Jet OLEDB:Link Datasource") = _ strPath & strName & IIf(DefaultData, "Data.Mdb", ".mdb") End If 'If an error occurs, exit the loop If Err.Number Then Exit For End If Next tdfRelink End With 'Remove the progress meter Call SysCmd(acSysCmdRemoveMeter) 'Clear the status bar vntStatus = SysCmd(acSysCmdClearStatus) 'Return whether or not an error occurred ReLink = (Err = 0) End Function

The Relink function receives two parameters. The first parameter is the name of the database to which the function will attempt to link. The second parameter is a Boolean variable that designates whether the database is considered the default database.

The function begins by modifying the status bar. It then creates a Catalog object and an instance of a custom class called DBInfo. The ActiveConnection property of the Catalog object is set equal to the Connection property of the current project. Next the FullName property of the DBInfo class is set equal to the name of the file that is passed as a parameter to the function. The DBInfo class extracts the path and the filename from the full filename. Just as with the VerifyLink function, the ReLink function uses a For…Next loop. As it loops through each table in the database, it attempts to establish a link to a database with the name passed as a parameter to the Relink function.

This is where the DefaultData parameter comes into play. The first time that the LinkTables routine calls the Relink function, it passes the name and path of the application database as the first parameter, and True for the second parameter. The Relink function then attempts to link to a database located in the same folder as the application database, but with the word

Data appended to the end of the filename. For example, if the application database is named Membership, the Relink function looks for a database called MembershipData in the same location as the application database. If it is successful, it returns True, and if it is unsuccessful, it returns False. I use this method to attempt to re-establish the link because I commonly place both the application and data databases on a client's network, both in the same folder. When I do this, I employ a naming convention where the data database has the same name as the application database, but with the word

Data appended to it.

If no data database with the expected filename is found in the folder where the application database is located (False was returned from the Relink function), the LinkTables routine uses the FileDialog object to display a File Open dialog box. This gives the user the opportunity to locate the data database. The filename and path that the user selects in the dialog box are passed to the Relink routine, along with False as the second parameter. Because the user has selected the file that she believes contains the data, there is no reason to append the word

Data onto the filename. Once again, the Relink routine loops through the Tables collection of the Catalog object, attempting to re-establish the broken links. If successful, it returns True, and if unsuccessful, it returns False. If False is returned from the second call to the Relink function, the LinkTables routine exits the Access application.

Using Replication to Efficiently Distribute Your Application

You might not want to rebuild and redistribute Setup disks each time you change the design of your application database. Not only is this time consuming, but it's also difficult to ensure that each user runs the Setup process to obtain the application database. If your organization is networked, it generally is much more effective to distribute application updates using replication. This involves making changes to the Design Master and then synchronizing with a hub after the changes are completed and tested properly. Replication is covered briefly in Chapter 20, "Developing Multiuser and Enterprise Applications," and in detail in

Alison Balter's Mastering Access 2002 Enterprise Development .