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

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:

Click to select Tables in the list of objects.

Click to select the linked table that you want to convert to a local table.

Select Edit, Copy.

Select Edit, Paste. The Paste Table As dialog appears (see Figure 19.10).

Figure 19.10. You use the Paste Table As dialog to make a Local Table from a Linked Table.

Type a name for the new table.

Select Structure and Data to copy both the structure of the linked table and its data, or select Structure to copy only the structure of the linked table.

Click OK.

The linked table now appears as a local table within the current database.