How best to manage links to tables in local Access databases?

[John Faughnan]

For reasons too obscure to describe, I very much like to segregate my
overall database into several .mdb files. The main file contains
queries and joins, the other files contain only tables.

Performance is surprisingly good. There's only one problem.

The links are hard coded full specified DOS pathnames, as in
c:\work\mystuff\access\mydatabase.mdb.

So if anything in the path is moved or renamed, the links break. The
Linked Table Manager doesn't know how to fix or manipulate these links
(though it does work for ODBC links). Link repair requires deletion
and restoration.

Are there any work arounds for these limitations? Perhaps an external
utility that would manage these links and maintain them externally --
a kind of a meta database file manager?

Thanks!

john
(e-mail address removed)

PS. Ever notice that compacting a database wipes out NTFS metadata --
such as comments? I can see why IFS/Longhorn has receded into the
distance! Today Microsoft apps don't even manage simple things like
basic NTFS metadata and relative paths.

meta: jfaughnan, jgfaughnan, Microsoft Access, Access 2002, Access
2003, file linking, indirection, redirection, UNC, pathname, path,
broken, break

 

[[MVP] S.Clark]

The LTM works, but it's very manual, especially if you have more than one
Data.mdb. When I do that, I use a table to store the table names with their
database names. Then through VBA code loop through the table and reattach
from the respective .mdb.

 

[John Faughnan]

The LTM works, but it's very manual, especially if you have more than one
Data.mdb. When I do that, I use a table to store the table names with their
database names. Then through VBA code loop through the table and reattach
from the respective .mdb.
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Steve, on my machine the LTM shows nothing when the linked table is
stored on my physical drive. Is this a bug? I could try reinstalling.
LTM works with tables I address via ODBC.

I don't have the expertise to reimplement your approach. Have you
productized it or made it more broadly available? Are you aware of a
utility that is packaged for relatively inexpert use?

Thanks for the help!

john
(e-mail address removed)