Update linked table connection when moving servers

[pl456]

I have an old access 97 db that links tables to an SQL db. We have recently moved our SQL db's to a new server and therefore the embedded connection strings no longer point at the correct server.

I can open MySysObjects table and veiw the connection strings but cannot change the server address in this table.
Does anbody know how I can do this or update the links without linking each table again?

 

[KenHigg]

I think you can do this with Tools->Database Utilities->Linked Tables Manager.

???

 

[namliam]

In code you can set the connection string of a linked table.

Check out the TableDefs object to find all tables in the database, then
For each table in tabledefs
change the connection to what you want/need it to be.... I have that exact code at home... but not with me atm.

 

[pl456]

Thanks.

I had to install the advanced wizard for the link table manager. I thought it was working but then it stopped (there must be some conflict as I have access office 2007 installed as well as 97).

I eventually got so sick of it that I converted the DB to access 2007, used the link manager in that and well, it kind of worked. What I now get is my sql login box pop up asking for my password, I submit that and it fails. However I can open 1 table submitting the same password and that works fine, then all the other tables can be opened (there is nothing special about this table). It has to actually be this 1 table that I attempt to open first or non of them work.

Perhaps you can let me have the code (you may... I say may... more like will need to explain how I apply it).

Cheers

 

[KenHigg]

I think you may need to re-connect via the odbc manager where I think it lets you save the login password stuff....