Automaticly replacing link path's to tables and queries in back-end databases

[jschenk]

Hi we are moving hunderds/thousends of Access databases from one network drive to another, also the path on the drive is changing.

How can I using VB.Net (prefered) or VBA replace all old links to tables and queries with new links.

I want to browse programmaticly through a directory tree and modify all databases I find, so an external vb.net application is easier then importing and running VBA code in every database)

I do have a table which translates old path's to new paths, so I need code which does the following

1. List all the linked queuries and tables inclusing their name and filepath
2. construct the new name and filepath
3. disconnect (unlink) the old table/query
4. reconnect (link) the same table/query from the new location

Perhaps 3 and 4 can be combined if I somehow can replace the path of a linked table/query as a property.

I hope the question is a bit clear, we are working with Access 2003

 

[DCrake]

From past experience you have to be in the front end database to change the links to the back end database. How have you managed to accumulate so many databases?

 

[jschenk]

From past experience you have to be in the front end database to change the links to the back end database. How have you managed to accumulate so many databases?

Yes I know I have to enter the front-end.
So many databases, 10 years working with 400+ users does the job

 

[DCrake]

If the majority of the databases have been created by your users, then I assume they are resident on their own machines.

Send them all an email with instructions on how to use the Link Table Manager to refresh the links to the back ends.

 

[jschenk]

If the majority of the databases have been created by your users, then I assume they are resident on their own machines.
Send them all an email with instructions on how to use the Link Table Manager to refresh the links to the back ends.

Alle the databases are on a fileserver.
I'm not looking for an alternative to the method I mentioned before, i just would like to know how to programmaticly do this
Besides, the fileerver will be migrated over night, so i I ask the users to do this they will surely no be able to do it in the time available (if they know which databases they have and know where to find them )

 

[boblarson]

Are ALL of the links only to other Access databases or are there links to spreadsheets, text files, SQL Server (or other RDBMS)?

 

[jschenk]

Are ALL of the links only to other Access databases or are there links to spreadsheets, text files, SQL Server (or other RDBMS)?

All front-end databases connect to MS Access back-end databases

 

[boblarson]

Check this out:

http://www.fabalou.com/Access/Modules/refreshtables.asp

 

[DCRAZYPRINCE]

Hi,

Was this issue resolved. Please let me know how did you resolve this. I have same issue now and I have many databases.