Why can't I re-link a table with vba using the CurrentDB() method directly?

Question

Could anyone explain why the first code example works but the second doesn't?

This re-link code DOES work:

Dim db As Database
Dim sNewLinkAddress As String

sNewLinkAddress = "C:\temp\backend.accdb"
Set db = CurrentDb

db.TableDefs("table1").Connect = ";Database=" & sNewLinkAddress
db.TableDefs("table1").RefreshLink

This re-link code DOES NOT work, but no error messages are given:

Dim sNewLinkAddress As String

sNewLinkAddress = "C:\temp\backend.accdb"

CurrentDb().TableDefs("table1").Connect = ";Database=" & sNewLinkAddress
CurrentDb().TableDefs("table1").RefreshLink

What concerns me is that there is a fundamental difference that I'm unaware of between using the Database object directly returned by CurrentDB() and using a variable 'db' that is set to the Database object returned by CurrentDB(). To my mind both ways should be identical, but obviously I'm wrong!

In the past I have used CurrentDB() directly for various things such as opening a recordset with no problem. There seems to be a specific issue with re-linking tables. Any ideas to what's going on here?

I'm using access 2007, but the same issue applies to 2003 also.

mwolfe02 mwolfe02 · Accepted Answer · 2011-04-15T14:26:22

Every time you call CurrentDb() it returns a new instance of the database object.

To illustrate what I mean I'll use letters to represent the database instance (the letters are arbitrary and used only to make it easier to follow the logic).

So in your first example you are using assigning the return value of CurrentDb to a database object db. Then each time you call db you are referring to the same database object instance:

Set db<A> = CurrentDb

db<A>.TableDefs("table1").Connect = ";Database=" & sNewLinkAddress
db<A>.TableDefs("table1").RefreshLink

In your second example you make multiple calls to the CurrentDb function and each call returns a different database object instance:

CurrentDb()<B>.TableDefs("table1").Connect = ";Database=" & sNewLinkAddress
CurrentDb()<C>.TableDefs("table1").RefreshLink

The upshot here is that when you call RefreshLink on the second line you are doing it against a brand new instance of the database object; ie, one whose "table1" Connect property has not been changed.

Why can't I re-link a table with vba using the CurrentDB() method directly?

1 Answers