linking to tables using relative path?

[JethroUK©]

I have a split database (Front & Back) - for some reason access cant link to
the back end tables using a relative path (even tho the front and back are
in the same folder) - using absolute path is a pain because i have to
recreate the link every time i'm on a different machine (still developing
it) - having the same trouble with all the mail-merge docs which are
absolute

I imagine there's a way to lose the absolute path in code:

Dim tbl

For Each tbl In CurrentDb.TableDefs
tbl.Connect = ";DATABASE=/CLAIT2006_be.mdb"
Next

but it doesn't work - any pointers much appreciated

 

[Martin]

CurrentProject.Path might be what you need - you can concatenate it in with &

 

[Allen Browne]

The Connect property cannot contain a relative link. It must be a fully
qualified path name.

The loop is then possible, but don't forget to skip the system tables and
non-linked tables, and RefeshLink.

 

[JethroUK©]

i have the code working:

Dim tbl As TableDef
For Each tbl In CurrentDb.TableDefs
If (tbl.Attributes And dbSystemObject) = 0 Then tbl.Connect =
";DATABASE=" & CurrentProject.path & "\CLAIT2006_be.mdb"
Next


alas i'm not sure where to put it? the dbase generates an error (broken
links) before it even runs the code - any clues on that?

 

[JethroUK©]

just to qualify - i have a form open on startup and the i have tried the
code in Form Load, Form Open & Form Activate, neither is fired early enough
to repair the broken-link error

 

[Martin]

How about the AutoExec macro? You'll need to put your code into a function
rather than a sub and place it on an ordinary module rather than one attached
to a form. Then, in a macro called AutoExec (which makes it run
automatically when you first open the database), choose RunCode and type the
name of your function at the bottom.

 

[Allen Browne]

You need to use an *unbound* form for your startup, so it is not trying to
load records.

Put your code into the form's Open event.
Then open the real form, and cancel the Open event for the unbound startup
form.

 

[JethroUK©]

Thanks for tip - rather than create another form, open, close, etc - i still
used the existing form but left it unbound until a connect the tables - for
the benefit of anyone else with similar problem:

Private Sub Form_Load()
Dim tbl As TableDef
For Each tbl In CurrentDb.TableDefs
If (tbl.Attributes And dbSystemObject) = 0 _
Then tbl.Connect = ";DATABASE=" & CurrentProject.path &
"\CLAIT2006_be.mdb": tbl.RefreshLink
Next

RecordSource = "Main Query"