Avoiding Multiple Tables and Links
One of the big tricks to using the TransferDatabase method is being aware of how it names the table or link that it creates. It doesn't overwrite an existing table. If the current database already contains a table or link with the name that you specify in the localTableName argument, Access creates a new table or link with a number added to the name.
For example, if CCSecureLocal already exists when you run the code to import its data, Access creates the new table as CCSecureLocall. Run the code again, and you get CCSecureLocal2, then CCSecureLocal3, and so forth. The tables (or links) just keep piling up, which isn't good.
You can solve the piling-up problem by writing a general-purpose procedure that always deletes the existing table (if it exists) before creating the new table. You can set things up so that you just have to copy and paste the whole thing into any code that needs to import or link to an external table. Look at a couple of examples.
The procedure in Listing 13-1, named ImportQry, imports a query from an external database. It ensures that you don't end up with multiple linked tables by first deleting any previously imported copy of the table by the same name.
Listing 13-1: Getting Data from an External Query
'Import a table snapshot from an external query. Public Sub ImportQry(dbPath As String, extQry As String, _ localName As String) 'Loop through the AllTables collection. Dim tbl As AccessObject, thisDB As Object Set thisDB = Application.CurrentData
Listing 13-1 (continued)
|
For Each tbl In thisDB.AllTables | |
|
'If the local table already exists... | |
|
If tbl.Name = localName Then | |
|
'If table is open... | |
|
If tbl.IsLoaded Then | |
|
'...close the table. | |
|
DoCmd.Close acTable, localName, acSaveNo | |
|
End If | |
|
'...delete the local table. | |
|
DoCmd.DeleteObject acTable, localName | |
|
End If | |
|
Next tbl | |
|
'Local table gone, import the query now. | |
|
DoCmd.TransferDatabase acImport, "Microsoft Access", | |
|
dbPath, acTable, extQry, localName | |
|
'All done. | |
|
End Sub |
You can just copy and paste this entire procedure into any standard module in your front-end database. Then, in any code that needs to import query results from an external database, call the procedure with the syntax
Call ImportQry("pathToDB","extQry"," localName") where
1 pathToDB is the full path and name to your external database.
1 extQry is the name of the query in that external database you want to import.
1 localName is the name as it will appear in the current database.
For example, the following code imports data from the CCSecureQry in C:\SecureData\MOMSecure.accdb into a local table named CCSecureLocal. The whole procedure is bound to the On Click event property of a hypothetical button named ImportBttn:
Private Sub ImportBttn_Click() Call ImportQry( _
"C:\SecureData\MOMSecure.accdb", _ "CCSecureQry", "CCSecureLocal")
End Sub
Listing 13-2 shows a similar procedure for linking to external tables — a procedure named LinkToTable that can set up a link to any external Access database table. Before doing so, it deletes the existing link, if any, to avoid multiple links to the same table. It's basically the same code as the preceding ImportQry() procedure. However, it sets up a link to the external table by using acLink on the TransferDatabase method:
Listing 13-2: Linking to a Table in an External Database
Public Sub LinkToTable(dbPath as String, extTbl as String, localName As String) 'Loop through the AllTables collection. Dim tbl As AccessObject, thisDB As Object Set thisDB = Application.CurrentData
For Each tbl In thisDB.AllTables
'If the local table already exists... If tbl.Name = localName Then 'If table is open... If tbl.IsLoaded Then '...close the table.
DoCmd.Close acTable, localName, acSaveNo End If
'...delete the local table. DoCmd.DeleteObject acTable, localName End If Next tbl
'Local table gone, import the query now. DoCmd.TransferDatabase acLink, "Microsoft Access", _ dbPath, acTable, extTbl, localName
'All done. End Sub
Once again, you can just copy and paste the entire procedure, as-is, into any standard module in your database. When you want to set up a link to an external table, call the function by using the syntax
Call LinkToTable("extDB", "extTable", "localName")
For example, to link to a table named CCSecure in a database named C:\SecureData\MOMSecure.accdb — and ensure that you don't re-create the previous link — just call the procedure from your code by using the syntax
Call LinkToTable("C:\SecureData\MOMSecure.accdb", _ "CCSecure", "CCSecureLinked")
Post a comment