Update linked table definitions

[DaveJHPT]

If a front-end database has links to many tables in a back-end database and the back-end is moved, is there an easy way to update all the table links in the front-end in one go, or do you have to set up all the links again one at a time?

Hoping there's a quick way...

Dave

edit: just realised the previous post asks exactly the same thing ( ), but that hasn't elicited a solution yet ( ).

 

[jrjr]

Tools/Database Utilities/Linked Table Manager
Check all boxes for the tables you want to link. Check the box for "Always Prompt for new Location" Click OK. Then you will browse to the new location of the tables.

 

[apoll0]

Well, that's what we already do. We're wondering if you can program the front end to re-link itself if we know that the backend is in the same folder as the front end. (I chose to have users link to the front end on a share drive rather than distributing them individually - a lot easier to maintain)

 

[DaveJHPT]

The one-off manual method is good enough for me!

Thanks for your help.

Dave

 

[jrjr]

Well, that's what we already do. We're wondering if you can program the front end to re-link itself if we know that the backend is in the same folder as the front end. (I chose to have users link to the front end on a share drive rather than distributing them individually - a lot easier to maintain)

I do the same thing - having the front end on the server. Seems to work well like that.

I played around trying to link to BE simply by having the DB name in the path statement and it does not seem to work. It looks like a full path statement is required. This works in web site coding and makes things easier if you move your web site around but I guess Access is a bit more particular.

I can relate to your need as I just went through IT changing things with no warning. Then I had to get a link out to all the DB users again. A pain to say the least but still easier than trying to deploy the program or to relink again if they had it on their local machine.

Select * from tblUsers where User=hasaclue
No rows returned

 

[allan57]

I use the following method to change my linked table destination ie from a
user enviroment to a development area(Access 97) on XP:

Dim dbs As Database
Dim tdf As TableDef

On Error Resume Next

Set dbs = CurrentDb
dbs.TableDefs.Refresh

For Each tdf In dbs.TableDefs
With tdf
If Len(.Connect) > 0 Then

'Current Location
If .Connect = ";DATABASE=N:\Access_Tables\User_Area\DB_Name_be.mdb" Then

'New Location
.Connect = ";DATABASE=N:\Access_Development\DB_Name_be.mdb"
.RefreshLink

End If

End If

End With

Next

Set tdf = Nothing
dbs.Close
Set dbs = Nothing