How to Quickly Update a Linked Table when the External File Name is Changed

Linked tables can be fantastically useful (although not all the time of course) – particularly when you’re dealing with external info that regularly changes. A typical example might be when a supplier gives you access to their current monthly price file. But what happens when they issue the file next month, and the file name changes from “Pricelist-01-01-2016” to “Pricelist-01-02-2016”? The first thing that will happen is your link will break, so you’ll have to manually update it. Every time they change the file name. That goes for table names too (of course!). Is there a way to do this quickly and easily you ask? We’re glad you asked – read on and find out how…

Setting the scene – the scenario

In this article, I’m going to use a fictional scenario – but one I’m sure you’ll quickly recognise!

Every month, Acme trading send us an updated price list for all the consumables used at DataNumen. So that we know how much we’ll be spending on stationary each month, we link to that file in our office management database.

The trouble is, even though the format of the file stays the same, the file name changes every month. Last month it was “stationary costs Jan 2017.xls”, this month it’s “stationary costs Feb 2017.xls”.

Not much of a change I’m sure you’ll agree, but unless we go through the trouble of renaming the file manually (once we’ve moved the old file out of the way or deleted it), or go through the process of relinking the table through the linked table manager in Access.

Because we didn’t want to do this, we created the following code to do it instead – much easier as I’m sure you’ll see:

Public Sub UpdateLink (tableName As String, newFileName As String)
    Dim objDB As Database
    Dim objTableDef As TableDef
    Dim newConnect as String
    
    Set objDB = CurrentDb
    Set objTableDef = objDB.TableDefs(tableName)

    'format of the connection string in our case, for example, is:
    ' Excel 5.0;HDR=YES;IMEX=2;DATABASE=File name including path and extension type
    newConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & newFileName
    objTableDef.Connect = newConnect
    objTableDef.RefreshLink

    Set objTableDef = Nothing
    Set objDB = Nothing
End Sub

Explaining the code

As you can see, we pass the (linked) table name, along with the name of the new file that the table should be linked to. The file name should include the full path to the file. One potential area that you may struggle with at the start is getting the correct format for the connection string to place into the “newConnect” variable. While there are plenty of sources for finding out the correct format, one of the easiest that I’ve found is to simply look at the connection string of the current linked table. To do that, simply add the following line directly below the “Set objTableDef = objDB.TableDefs(tableName)” line:

Debug.Print (objTableDef.Connect)

That will print the existing connection string out into the debug/immediate window of the code editor (if that is not already visible, press CTRL-G from within the VBA code editor screen to toggle visibility of the Immediate window prior to running the code.

A warning

As always, don’t forget that while the code snippet above can help you save time when you need to change the file a table is linked to, what it can’t do is help you if you encounter any Access file damage, so make sure you keep backups and know where to turn if all else fails.

Author Introduction:

Mitchell Pond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair SQL damage and excel recovery software products. For more information visit www.datanumen.com