Relative path for linked tables (1 Viewer)

[AccessKid]

Hi,

I am using separate/split databases for my interfaces (forms, reports, modules) and data but both mdbs reside in the same folder. This folder happens to be in a network drive with a letter different that the local drive of my user. I do the modifications on the network files and copy the updates to the user when I'm done. However, when the interface mdb is copied to the user's disk, the linked tables' paths point to the network file instead of the user's disk.

Is there a way to make the linked table path relative?

 

[rpadams]

Not elegant - just quick and dirty

One way to do this is to write a very small autoexec.bat on the user's computer that assigns a subdirectory to a drive letter on the server. For example, keep the user's FE/BE on a local drive such as C:\myprograms. If your network drive is F:, the batch file would have the line

subst F: C:\myprograms

When you make the links to the BE they will point to F:, but this is OK since the users computer is being tricked into pointing to the same place.

Some of the old-timers out there will recognize this as an old DOS trick when networks first came into use. Developers reversed this so they could write network software on their own PC's.

 

[AccessKid]

Thanks, but then again my users will "lose" their network drive which they happen also to access for other reasons.

I guess I'm back to relinking everytime I update the user's copy. Or maybe I should duplicate the user's local folder on my PC and modify the programs from there instead of the network drive.

I just wonder why Access should behave like it was developed 10 years ago... Hmmmm.

 

[Tim K.]

You need to relink the tables upon the open of the front end.
Check this post out.

 

[AccessKid]

will try this

This may serve the purpose that I have in mind. Thanks Tim.

 

[Pat Hartman]

If you link the tables with the UNC convention, you won't have the issue.

1. Open the Link table wizard
2. Check the box that says "always prompt"
3. Select all the tables and press OK
4. In the file name box, type \\YourServerName\FullPath\YourBackend.mdb

Using this method, it doesn't matter if your users have a drive mapped at all let alone what letter it is.

 

[AccessKid]

Hi Pat,

I think I was sidetracked from my original intent in my original question.

I would like to have a path that points to the same location as the front-end mdb but avoiding the machine name so that I can move the mdbs freely from the network location to the local hardisk location as needed.

ie. the path for currentfolder\mybackend.mdb

 

[Pat Hartman]

Access doesn't support a relative path. Try writing code to verify the current links when the db opens. If the links are invalid, prompt the user to "find" the correct backend and display the common file dialog box using the Windows API. You can find code for all of this by searching the archives.

 

[AccessKid]

Thanks. I'm just wondering if this will give the user unnecessary control over how the program works. Is there a function to get the current path of the FE mdb and I can just plug this in to the path of the BE without the user intervention?

 

[Tim K.]

As I mentioned in the link above, if you use Access 2000 or higher, you can use CurrentProject.Path to get the current path of the FE mdb.

If you use Access 97, you'll need InstrRev() function to help. Check this post for the function.

Then use CurrentDb.Name to help to to get the current path of the FE mdb like this.

=Left(CurrentDb.Name, InstrRev(CurrentDb.Name, "\"))

 

[AccessKid]

Thank you all guys. You've been most helpful.