dynamically linking tables in access

[Andy Levy]

I have an access frontend database with linked tables in a backend database
..

The backend and frontend database are always in the same directory. But the
directory moves a lot and access is not able to findthe backend database
because it is an absolute reference to a position on my drive.

I would like to know if it is possible to make the referencing dynamic - so
that the linking occurs at the point i load my switchboard.

Thanks

Andrew

 

[Graham Mandeno]

Hi Andrew

CurrentProject.Path will give you the path to your front-end (Access 2000
onwards).

To respecify the back-end file, you change the Connect property and then
call the RefreshLink method for each linked table.

For example:

Dim db as Database, tdf as TableDef, sConnect as String
sConnect = ";DATABASE=" & CurrentProject.Path & "\MyData.mdb"
Set db = DBEngine(0)(0)
For Each tdf in db.Tabledefs
If tdf.Attributes and dbAttachedTable Then
tdf.Connect = sConnect
tdf.RefreshLink
End If
Next tdf

 

[John Vinson]

I have an access frontend database with linked tables in a backend database
.

The backend and frontend database are always in the same directory. But the
directory moves a lot and access is not able to findthe backend database
because it is an absolute reference to a position on my drive.

I would like to know if it is possible to make the referencing dynamic - so
that the linking occurs at the point i load my switchboard.

There are a number of folks who have written VBA code to do this. One
good one is from Dev Ashish on the Access Web:

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