How do I create a function to redirect a table link from one data.

[kusumo]

I need to create a function to redirect a table link from one database to
another.

The input of the function will be the location of the new database.

Thanks for the help.

 

[Nikos Yannacopoulos]

Kusumo,

Here's some sample code that does just that for all tables in the
database. If you just want to do it for a particular table then remove
the loop delimiters and hardcode the table name (or pass that one too as
a paranmeter).

Function change_links(oldBE As String, newBE As String)
Dim db As DAO.Database
Dim tbl As DAO.TableDef

Set db = CurrentDb()

'oldBE = "C:\SomeFolder1\db1.mdb"
'newBE = "H:\SomeFolder2\db2.mdb"

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, oldBE, newBE)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next

End Function

Note: To run this code, it is required to have an appropriate DAO Object
Library reference. While in the VB editor window, go to menu item Tools

References; check if a Microsoft DAO reference is present among the

ones checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

HTH,
Nikos