Changing the source of linked tables

[Milan]

Hello,

I have this problem. I have to change a source (connection string) of
linked tables using VBA. All the tables are of MS Jet type. I tried to
do it this way:

-------
CurrentDb.TableDefs("X").Connect = "MS
Access;PWD=xxx;DATABASE=C:\DB\Y.mdb;TABLE=X"

CurrentDb.TableDefs("X").RefreshLink
-------

but the source was not changed and no msgbox with error description
displayed. I use Access XP.

Thank you very much for your help.

Milan

 

[Marshall Barton]

I have this problem. I have to change a source (connection string) of
linked tables using VBA. All the tables are of MS Jet type. I tried to
do it this way:

-------
CurrentDb.TableDefs("X").Connect = "MS
Access;PWD=xxx;DATABASE=C:\DB\Y.mdb;TABLE=X"

CurrentDb.TableDefs("X").RefreshLink

FIrst, you need to use an object variable for the database
objecy. Every use of the CurrentDb() function creates a new
copy of the db object and your TableDef goes out of scope as
soon as the statement finishes executing.

The connect string is not that complicated. Try this:

Set db = CurrentDb()
Set tdf = db.TableDefs("X")
tdf.Connect = ";DATABASE=C:\DB\Y.mdb"
tdf.RefreshLink
Set tdf = Nothing
Set db = Nothing

 

[Milan]

Thank you very much for your help!

Milan