Updating Linked Table Source Using VBA (97)

Topic

New Reply

I have a linked table called “enable” and I would like to change the source of the data using VBA.

I have the following 2 lines in a function:

CurrentDb.TableDefs(“enable”).Connect = “DATABASE = j:groupsvaluationewmethfpvafpen0406.mdb”
CurrentDb.TableDefs(“enable”).RefreshLink

The 0406 is the year and month of my datafile. The table is currently linked to the 0405 datafile. When I execute this function, it seems to have no effect. The link stays as 0405. What am I doing wrong?

Thanks.

Reply | Quote

Replies

This is because CurrentDb creates a new object each time you use it, so the second instance is not connected to the first one. Instead, use an object of type DAO.Database that you set to CurrentDb. That way, its gets created only once:

Dim dbs As DAO.Database
Set dbs = CurrentDb
With dbs.TableDefs(“enable”)
.Connect = “;DATABASE=j:groupsvaluationewmethfpvafpen0406.mdb”
.RefreshLink
End With
Set dbs = Nothing

Reply | Quote

Thanks so much, Hans. This works perfectly now. I suspect that this has been my core problem causing all of my posts in the Access board recently. I assumed that CurrentDB behaved just like ActiveWorkbook in Excel VBA.

Reply | Quote

Thanks so much, Hans. This works perfectly now. I suspect that this has been my core problem causing all of my posts in the Access board recently. I assumed that CurrentDB behaved just like ActiveWorkbook in Excel VBA.

Reply | Quote

This is because CurrentDb creates a new object each time you use it, so the second instance is not connected to the first one. Instead, use an object of type DAO.Database that you set to CurrentDb. That way, its gets created only once:

Dim dbs As DAO.Database
Set dbs = CurrentDb
With dbs.TableDefs(“enable”)
.Connect = “;DATABASE=j:groupsvaluationewmethfpvafpen0406.mdb”
.RefreshLink
End With
Set dbs = Nothing

Reply | Quote