لطفا منتظر باشید ...
Refreshing and Removing Links
Refreshing links refers to updating the link to an external table. It is done when the location of an external table has changed. Removing links refers to permanently removing a link to an external table.Access cannot find external tables if their locations have moved. You need to adjust for this in your VBA code. Furthermore, there might be times when you want to remove a link to external datawhen it is no longer necessary to use the data, or when you have permanently imported the data into Access.
Updating Links That Have Moved
To refresh a link using VBA code, simply redefine the Jet OLEDB:Link Datasource. Listing 19.10 shows the code to refresh a link.
Listing 19.10 Refreshing a Link
Sub RefreshLink()
Dim cat As ADOX.Catalog
Dim tdf As ADOX.Table
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
tdf.Properties("Jet OLEDB:Link Datasource") = _
strNewLocation
End Sub
You can modify this routine to prompt the user for the directory containing the data tables, as Listing 19.11 shows.
Listing 19.11 Prompting the User for the Database Path and Name
Sub RefreshLink()
'Initiate error handling
On Error GoTo RefreshLink_Err
Dim cat As ADOX.Catalog
Dim tdf As ADOX.Table
Dim strNewLocation As String
Dim strTemp As String
'Instantiate a Catalog object
Set cat = New ADOX.Catalog
'Set the ActiveConnection property of the Catalog
'object to the connection associated with the
'current project
Set cat.ActiveConnection = CurrentProject.Connection
'Point the TableDef object at the tblClients table
Set tdf = cat.Tables("tblClients")
'Attempt to retrieve the Name property of the table
strTemp = tdf.Columns(0).Name
'Exit the routine if all goes well
Exit Sub
RefreshLink_Err:
'If an error occurs, prompt the user for the new name
'and location
strNewLocation = InputBox("Please Enter Database Path and Name")
'Set the properties of the TableDef object to the
'information provided by the user
tdf.Properties("Jet OLEDB:Link Datasource") = _
strNewLocation
Set cat.ActiveConnection = CurrentProject.Connection
Set tdf = cat.Tables("tblClients")
'Try to grab the name property again
Resume
End Sub
This routine points a Table object to the tblClients table. It then attempts to access the name of the first column in the table. If an error occurs, an input box prompts the user for the new location of the database. The routine modifies the Jet OLEDB:Link Datasource property for the database to incorporate the new location. It then resumes on the offending line of code. You should modify this routine to give the user a way out. Resume throws the user into an endless loop if the database is not available. An enhanced routine (see Listing 19.13) is presented later in the "Practical Examples" section of this chapter.
Deleting Links
To remove a link using VBA code, simply execute a Delete method of the Tables collection of a Catalog object connected to the database, as shown in Listing 19.12.
Listing 19.12 Removing a Link
Sub RemoveLink()
Dim cat As Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
cat.Tables.Delete ("tblClients")
End Sub
Making a Local Table from a Linked Table
New to Access 2003 is the capability to convert a linked table to a local table. The process works like this:
Figure 19.10. You use the Paste Table As dialog to make a Local Table from a Linked Table.
The linked table now appears as a local table within the current database.
