We’ve said it before, but it’s worth repeating – linked tables can be really useful in your databases for a number of reasons. But one big drawback of linked tables is performance – running queries against them, especially if they aren’t Microsoft Access tables (i.e. if they’re Excel, CSV, or DBF files). They can be painfully s l o w to work with. There are a few ways you can work around that – we’ll work with one way here, and that’s to convert a linked table into a local table. Sure – you could do this manually by copying/pasting tables, but if you have a lot to handle, doing it in code is much, much easier, so let’s see how…
Why outlining your needs is important
First of all, let’s outline what we actually want the code to do – doing this makes it so much easier to actually create the code in the first place so I’d suggest you do this every time you’re thinking of creating some VBA code, it helps clarify your aim:
“Given the name of a linked table, import that table into the database as a local table to improve performance – optionally delete the link from the database at the same time”
Normally, when you’re converting a linked table into a local table, you’ll want to delete the link, but in the code sample below we’ve given you the option to keep the original linked file too but by doing this, your newly imported table won’t replace your linked table in your queries etc, so you won’t see the performance gain that a local table would give you. Once you’re comfortable that the code is working as you need, we’d recommend always deleting the original – after all, you’re not actually deleting the actual table, just the link to it.
A word of caution
Before we get to the code – just a quick word of caution, there’s not a lot you can do if you’re linking to a damaged Access database – if Access can’t read the file, it’s highly unlikely it’ll be able to import from it.
So – on to the code…
Sub MakeTableLocal(tableName As String, optional deleteOriginal As Boolean = True)
Dim DbPath As Variant, TblName As Variant
'get path of linked table
DbPath = DLookup("Database", "MSysObjects", "Name='" & tableName & "' And Type=6")
'Get the real name of the linked table (in case it has been given an alias in the link)
TblName = DLookup("ForeignName", "MSysObjects", "Name='" & tableName & "' And Type=6")
If IsNull(DbPath) Then
'Either a local table, or the wrong table name has been supplied, exit the sub
Exit Sub
End If
'delete linked table
If deleteOriginal Then
DoCmd.DeleteObject acTable, tableName
Else
'If we're not deleting the existing table we'll have to rename the imported table to avoid
'overwriting it etc
tableName = tableName & " - local"
End If
'import the table as a local, unlinked table
DoCmd.TransferDatabase acImport, "Microsoft Access", DbPath, acTable, TblName, tableName
End Sub
What the code is doing
Most of the code will be pretty obvious but there are some key points to note. Firstly, we get the detail of what file is being linked to by looking in the (usually hidden) Access system table “MSysObjects”. As it’s perfectly possible (and probable) that you will have given the linked table a new local alias, the next thing we need to do is to get the name of the original table – again, from the “MSysObjects” table.
Now that we have those details, as long as they’re valid (i.e. you’ve not supplied a misspelt table name to the routine), then we’re set to go so it’s simply a matter of a) deleting the link (if deleteOriginal is set to True), and importing the table.
Doing this will often give you pretty dramatic improvements in performance so if you’re using linked tables, I’d highly recommend that you check what gains moving those tables to local versions will give you – you may be surprised!
Author Introduction:
Mitchell Pond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair SQL Server file and excel recovery software products. For more information visit www.datanumen.com