Using a relative address for a linked table

[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!

 

[Guest]

Sorry, that is not possible.

 

[Guest]

Okay, different question. Is it possible to modify the MSysObjects table?

 

[Guest]

You can, but I wouldn't. What is your idea?

 

[John Nurick]

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.

 

[Guest]

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.

 

[John Nurick]

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.

 

[Albert D. Kallal]

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.

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....

 

[Guest]

THANKS!! I didn't know about CurrentProject. A whole new world has just
opened up. )