I searched and couldn't find anything on this specific issue.

I'm on the verge of designing a pretty big database and I've run into what may become a bit of a problem. There will probably be several backends in different locations. Should another backend ever be created or if the location for one changes, the user can use a form to refresh the links to the tables on that specific database.

The way I've done this so far is by having a piece of code that checks the tables for a connection string. If the table has a connection string, its a linked table, so the link gets deleted and replaced with the new link. Well this will no long work since there will be several backends and only 1 of them may need its tables relinked and using that code will delete all the table links, including the good ones.

Is there a way to make 'DoCmd.TransferDatabase' overwrite table names instead of giving the duplicate tables a number suffix? I think that doing this would be easier than retrieving the table names from the new backend and comparing them with the linked table names in the frontend so the old links could then be deleted.

Ended up doing it the hard way :( Its kinda slow, so if anyone knows a quicker way to do this, then let me know please!

Set dbs = CurrentDb
Set newdbs = OpenDatabase(tbFile, False, False, "MS Access;pwd=password")
For Each tdf In dbs.TableDefs

If Len(tdf.Connect) > 0 Then

For Each newtdf In newdbs.TableDefs

If newtdf.Name = tdf.Name Then

DoCmd.DeleteObject acTable, tdf.Name
Err = 0
On Error Resume Next
tdf.RefreshLink

End If ' newtdf.Name End If
Next newtdf

End If
Next tdf