Using a relative address for a linked table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to be able to use a relative, rather than an absolute, address when
specifying a linked table.

Suppose I have two Access databases with some linked tables residing in the
same subdirectory. When I move both of them to a different subdirectory, I
would like them to continue to work without re-linking.

Can someone please tell me how to do this? I'd prefer VB, but I'll take
anything you got! TIA, all!
 
Hi Carol,

As Dave says, linked tables don't use relative paths, full stop.

If instead you link the data by using an IN clause in a query you can
use a relative path, e.g.

SELECT *
FROM Classics IN 'Music.mdb';

The complication is that the path is relative to Access's current
directory, which generally isn't the directory that contains the current
database. I think you can fix this just by calling

ChDir CurrentProject.Path

before using the query and making sure that no other code subsequently
changes the current directory.
 
Well, here are a lot more words about my problem.

I have a group of four .mdbs that sort of travel around together. Each one
has a specific set of functions to do, and each contributes tables to all the
others.

When I copy these four dbs to other servers, all of them need to have some
linked tables refreshed. I was trying to set that up in VB so that it
happened automatically when a db was opened, basing relative locations of the
other 3 dbs on the location of the open db.

What I originally came up with was a module that gets the attached table
name and the location it's currently linked to using MSysObjects and
currentdb.name to get the current location of the db that's open. Then I
just create some strings using these three elements, delete linked tables,
and re-link.

I was hoping that there was some other way to do it that would either allow
me to use a relative address or update MSysObjects directly without having to
delete and re-link.
 
What you're doing is pretty much the usual way. Using
CurrentProject.Path instead of CurrentDB.Name simplifies it a little.
Using a relative path in a query as I suggested is only safe if you can
be certain that nothing else will alter the current directory.
 
Carol Grismore said:
I'd like to be able to use a relative, rather than an absolute, address
when
specifying a linked table.

Suppose I have two Access databases with some linked tables residing in
the
same subdirectory. When I move both of them to a different subdirectory,
I
would like them to continue to work without re-linking.
Click to expand...

Well, you can't get around re-linking, but you certainly can have code that
detects hat things are missing, and re-link FROM a relative position...

So, while it true you can NOT link relative, if your code detects a broken
link, the code can determine the relative path name and use that for
re-linking....

at the end of the day, you get essentially the same result. Your problem is
"without re-linking" is not possbile....

If you allow re-linking, then you can write code for this problem....
 
Back
Top