Redirect table links in many databases with VBA

[jDa]

My dept has an aging Access repository with dozens of tables. We have hundreds of Access files (report generators) that have links to the repository tables (over 4000 links in all). We are replacing the Access repository with a SQL Server repository. The Access Report Generators, however, will continue to exist. We need to update all the links to point to the new repository.

I've already written an applet that searches recursively through all our departmental folder structures and catalogs the links (by hitting the msysobjects tables of the RPGs). I have an Access table containing information about each link. Now I need a way to programmatically delete existing links and create links to the new tables. One program needs to do this for the hundreds of files referenced in the links catalog. The new tables have the same names and data structures as the old tables, so queries using the links will not be affected--only the links need to be changed.

Any solutions, or even ideas to try, would be much appreciated.

Thanks,

jDa

 

[jDa]

Problem solved. See basic code under "Can Link be changed w/o opening db"

From here it's just a matter of applying this technique within a loop.