How to dynamically change path for a linked table?

[John]

I have two Access .mdb files. DB1 contains just the forms code and DB2
contains the actual tables. DB1 has links to the tables in DB2. I want
to be able to move these two files into any folder and be able to have
DB1 access DB2. Since the links store the full path, I need to be able
to change the path when the program in DB1 starts up. Can someone tell
me how I can do this? Thanks.

John

 

[Allen Browne]

Loop through the TableDefs, and set the Connect property of each attached
table. Remember to RefreshLink.

If both databases will be in the same folder, the code in this link should
do it for you:
http://allenbrowne.com/ser-13.html

 

[Van T. Dinh]

See The Access Web:

http://www.mvps.org/access/tables/tbl0009.htm

HTH
Van T. Dinh
MVP (Access)

 

[Alex White MCDBA MCSE]

Hi John,

When linking tables, to improve performance 100%+ during linking,

link your first table and straight after open that table and leave it open,
link all your other tables, then close the first table, because you have a
connection to the back-end it speeds up the process of linking the other
tables. By the way my code is using ADO not DAO for linking. Have one table
in the front-end that has the location of the backend db in a field so that
you can relink easer.

dim strBEDatabase as string
Dim bolGood As Boolean
Dim adoTest As New adoDB.Recordset
strBEDatabase = "c:\mybe.mdb"
bolGood = RelinkTable("Adverts", "Adverts", strBEDatabase)
With adoTest
If .State = adStateOpen Then
.Close
End If
.Open "Select * from ADVERTS", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
End With
bolGood = RelinkTable("Applicants", "Applicants", strBEDatabase)

....

adoTest.Close




Function RelinkTable(strTable As String, strSourceTable As String,
strSourceDB As String) As Boolean
On Error GoTo Err_RelinkTable
Dim adocat As New ADOX.Catalog
Dim adotbl As New ADOX.TABLE
Set adocat = New ADOX.Catalog
Set adocat.ActiveConnection = CurrentProject.Connection
Set adotbl.ParentCatalog = adocat
On Error Resume Next
adocat.Tables.Delete strTable
On Error GoTo Err_RelinkTable
adotbl.NAME = strTable
adotbl.Properties("Jet OLEDB:Link Datasource") = strSourceDB
adotbl.Properties("Jet OLEDB:Link Provider String") = "MS Access"
adotbl.Properties("Jet OLEDB:Remote Table Name") = strTable
adotbl.Properties("Jet OLEDB:Create Link") = True
adocat.Tables.Append adotbl
Exit_RelinkTable:
Exit Function
Err_RelinkTable:
If Err.Number = -2147217860 Then
Exit Function
Else
DisplayError "ModTableFuncs", "RelinkTable", Err.Description,
Err.Number, Erl
Resume Next
End If
End Function

Hope it helps.