Changing a tabledef .connect property in VBA

Question

I am trying to do something that I have done a hundred times, but it suddenly won't work.

acc.AutomationSecurity = msoAutomationSecurityLow   'Remove Security Prompt
acc.OpenCurrentDatabase path & "Year " & Range("yr") & "\" & Range("yr") & Range("mo") & "\Year " & Range("yr") & Range("mo") & " GENERIC DB NAME.mdb", True

'update link
acc.CurrentDb.TableDefs("TABLE NAME").Connect = "MS Access;DATABASE=" & path & "Year " & Range("yr") & "\" & Range("yr") & Range("mo") & "\Year " & Range("yr") & Range("mo") & " OTHER DB.mdb"
acc.CurrentDb.TableDefs("TABLE NAME").RefreshLink

I don't know if this is related to the fact that we recently updated to Excel 2010 and the db is still ACCESS 2003 but this should be a simple bit of code to run. Nothing happens when I run the above code. The file opens the right way but the connection string never gets assigned to the table def object, ie. It runs without erroring, but the table is not linking to the new database. I have found the following code does work and have used it to get around the issue. I am still curious why I can't assign a value to the .connect property in the style 'application.currentdb.tabledef("TABLE").connect', but if I assign the currentdb to a new database object I can.

So I don't know why but if I use this it works

dim db as DAO.database
set db = acc.CurrentDb
db.TableDefs("TABLE NAME").Connect = "MS Access;DATABASE=" & path & "Year " & Range("yr") & "\" & Range("yr") & Range("mo") & "\Year " & Range("yr") & Range("mo") & " OTHER DB.mdb" 
db.TableDefs("TABLE NAME").RefreshLink 

Thanks for any help you can provide.

Answer 1

I ran into a similar problem. When trying to do this directly through CurrentDb (i.e. CurrentDb.TableDefs("foo").Connect) didn't work and didn't throw an error.

The reason is: Every time you refer to CurrentDB, it is a new instance.

In your second method, you created a database object that you set to CurrentDb and that's your second method works.

Long story short: Good:

Dim Db as Database
Set Db = CurrentDb
Db.TableDefs("foo").Connect = "New connection string"
Db.TableDefs("foo").RefreshLink

Bad:

CurrentDb.TableDefs("foo").Connect = "New connection string"
CurrentDb.TableDefs("foo").RefreshLink