لطفا منتظر باشید ...
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 .
