Linked tables on DB with varying drive letter.

[Guest]

Hi all,

I have been working on a Database that is used for several sites, each site
has their own data.

The problem I have run into is that some users work at more than one site
with the same laptop, on all sites the local drive is G: and so when working
at another site, the second sites local drive letter is changed to K:. This
of course means that the linked tables refer to the first sites data not the
second sites.

I was wondering if there is any way to make the link simply refer to the
directory that the user interface part of the program is stored in eg:
*\\DatabaseData.

Any suggestions would be great.

Thanks Aaron.

 

[Tony Toews [MVP]]

I have been working on a Database that is used for several sites, each site
has their own data.

The problem I have run into is that some users work at more than one site
with the same laptop, on all sites the local drive is G: and so when working
at another site, the second sites local drive letter is changed to K:. This
of course means that the linked tables refer to the first sites data not the
second sites.

I was wondering if there is any way to make the link simply refer to the
directory that the user interface part of the program is stored in eg:
*\\DatabaseData.

While a good idea the network share named is prefaced by the server
name. So that won't work. Unless I'm mistaken about share names of
course.

Upon starting up the app though I'd just open a recordset against a
table. If successful then you don't need to do anything. If it gives
you an error then you need to figure out where the current backend
resides.

What you could do though is store a list of the possible drive letter,
file path and file name. Then use an API call to see if one of
those exists.
http://vbnet.mvps.org/index.html?code/fileapi/fileexists.htm The
downside to this is that it will likely take two or three seconds, or
longer, for each one to be checked before the app finally finds the
right one. I'd likely give the user a list box containing the site
names so they can choose the local site and go from there.

Now that you've located the file path and name you can use the
following code to relink the tables.
Relink Access tables from code
http://www.mvps.org/access/tables/tbl0009.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[James]

Hi

In the linked table manager of your front end databases, just change
the location to the full UNC path, i.e.
instead of g:\databasedata refer directly to \\servername\sharename
\poss_sub_folder\databasedata

I do this in quite a few databases without any problems (that I can
tell anyway ;D)

Regards
James

 

[Tony Toews [MVP]]

In the linked table manager of your front end databases, just change
the location to the full UNC path, i.e.
instead of g:\databasedata refer directly to \\servername\sharename
\poss_sub_folder\databasedata

I do this in quite a few databases without any problems (that I can
tell anyway ;D)

Using UNC paths works. However Azz states that he is dealing with
multiple sites therefore I assume his servers have different names at
each of the sites.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm