Change the Database that a linked table references

[Himmel]

Hello,

I am using an access database to query a very large number of linked tables. Currently, the name of the database the linked tables point to is always the same name. However we are switching to a new system that uses versioned databases. Each time a new build of the database is published, a whole new database is created.

For example, we are currently on version 1847. The linked tables are set to look in "Database_1847".

A new build of the database is published as "Database_1848". The linked tables are still set to read from "Database_1847" which contains old data.

I have over 200 tables that need to be relinked to "Database_1848" and I would prefer not having to delete them and reimport them. Is there a VB command or function that can be used to update the database all my linked tables are linked to?

[Himmel]

Found it myself. :) In case anyone else wants to know how, here ya go. I go through ODBC so I've included the ODBC reference info. I believe you can leave it off, but I'd rather not chance it. :)

Dim db As DAO.TableDef
Dim newbuild As String
 
newbuild = InputBox("Please enter the database build:")
 
For Each db In CurrentDb.TableDefs
If Len(db.Connect) > 0 Then
 
    db.Connect = "ODBC;DSN=ODBC_DB_NAME;DATABASE=" & newbuild
    db.RefreshLink
    x = x + 1
 
End If
Next
MsgBox ("Update complete." & vbCrLf & vbCrLf & x & " tables have been updated to " & newbuild & ".")