Change Path Of Multiple Linked Tables at Once

[Neil]

My IT department is forcing me to move all of my databases to a new location.
I therefore will need to change all the paths on my linked tables. Is there
a way to do this in one shot vs. manually doing each table one by one through
the linked table manager?

Thanks!
Neil

 

[Douglas J. Steele]

See whether the code in http://www.mvps.org/access/tables/tbl0009.htm at
"The Access Web" helps.

 

[Klatuu]

You could write some VBA code to do the relink, but depending on how many
databases and how many tables, the manual approach may be faster.

 

[Neil]

What I was hoping to do is tell my dbase whether through code or some other
means to Replace O:\Prod\ with G:\yadayadayada.

Any slick way of doing that without too much pain?

I tried the code on the link and it does not compile. I am by no means a
programmer so if its as simple as copying and pasting code and updating the
path name in that code I can do that, but if its more involved I just have
not reached that level of programming.

Thanks again for your help and replies.

 

[Douglas J. Steele]

I suspect that the compilation error means you don't have a reference set to
DAO. (neither Access 2000 nor 2002 have the reference set by default).

Go into the VB Editor and select Tools | References from the menu. Assuming
that Microsoft DAO 3.6 Object Library isn't one of the selected ones at the
top of the list, scroll through the list until you find it, and select it.

If you want every database relinked, change

strMsg = "Do you wish to specify a different path for the Access
Tables?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Alternate data source...") =
vbYes Then
strNewPath = fGetMDBName("Please select a new datasource")
Else
strNewPath = vbNullString
End If

to

strNewPath = "G:\yadayadayada"

Note, though, that this code has to be run from within every database.

It should be possible to modify that code so that you can run it external to
each database (i.e.: just select which databases you want changed, and have
the code go at it), but that will involve some programming.

 

[David W. Fenton]

My IT department is forcing me to move all of my databases to a
new location.
I therefore will need to change all the paths on my linked
tables. Is there
a way to do this in one shot vs. manually doing each table one by
one through the linked table manager?

Are you saying that when you check off all the tables in the linked
table manager it doesn't do all of them at once?

Perhaps you have more than one back end? If so, here's a utility
that will do the job of reconnecting to multiple back ends:

http://dfenton.com/DFA/download/Access/Reconnect.html

 

[Robert_L_Ross]

Consider using something like Speed Ferret to go through and find the linked
path and change it - from the Speed Ferret interface. If you set up a
similar folder structure on the new location, you can do a 'find and replace'
of the path only.

Our IT group recently did the same thing to us and we were able to make the
change to 17 db's in about a half an hour.