Changing tabledef.connect property in VBA

Question

I am trying to do what I have done a hundred times, but it suddenly will not 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 do not know if this is due to the fact that we recently updated Excel 2010, and db is still ACCESS 2003, but it should be a simple bit of code to run. Nothing happens when I run the above code. The file opens correctly, but the connection string is never assigned to the def def object, i.e. It works without errors, but the table is not connected to the new database. I found that the following code really works and used it to get around this problem. I'm still wondering why I cannot assign a value to the .connect property in the style of 'application.currentdb.tabledef ("TABLE"). Connect ', but if I assign 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 had a similar problem. When I tried to do this directly through CurrentDb (ie CurrentDb.TableDefs("foo").Connect ) did not work and did not throw an error.

The reason is this: every time you refer to CurrentDb , it is a new instance.

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

In 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