Linked tables across network - invalid path error

[Andrew Fergus]

Hi All

I guess it's my turn to ask an Access question. I'm building a database that is consolidating a number of separate invoicing databases. While I am in development mode, I decided to link the important tables from the other databases so that I could do my testing on data that is changing (the other databases are still being used until the new database goes live). I didn't want to do a full import of a snapshot because I want to essentially run in parallel for my tests, hence the need for live data.

My issue is that the other databases are on another PC across the network and I frequently get an error of the type : "xxx\xxx\xxx.mdb is not a valid path - make sure the path is spelled correctly etc." (where the x's are the path and file name of the linked tables). The linked tables work one day and then after I turn the PC's off at night and restart them the following day, the links are then broken. I can browse to the other databases (there are 3 that I link to) across the network ok but I don't understand why the links keep breaking. My network has fixed IP addresses and it's a real nuisance setting up the linked tables every time I want to do more development / testing.

Can anyone help with this? Essentially my database can't see the tables that I linked across the network from one day to the next, even though I believe nothing has changed. I'm using Access 2K and the 2 relevant PC's are running Windows XP (has the linked tables with the live data) and Windows 98 (development PC).

TIA, Andrew

 

[Stubby Holder]

Hi Andrew,

Is it possible that someone is making changes to the tables you are linked to?
If the definition of the table stored in your db doesn't match the linked table, then the link needs to be refreshed.

I had a similar problem with an Oracle database I link to...I ended up just deleting & relinking the tabledefs via code on start up.

stubby

 

[tails]

Having the database stored on a network drive can have a few problems. Firstly, you have the mapped drive on the computer you built it on to lets just say [server1\file\filename] as the E drive. And on the other computer they could have it mapped to the F drive. (what ever drives you have them set up to.)
Second, When you map the drive to the server, you may not have clicked the reconnect at logon, so that drive will not map to the computer automatically, hence causing you to have to manually do it.
Thirdly, the linked table could be mapped to the wrong (or different) drive as the other computers again. Which will cause the same effect.

The way to fix this is to either, when you save the file, make the filename have the server mapping in it. eg 'SAVE AS' 'servername\folder\filename.filetype' should make it work.
also you could go around to the individual computers and set the drive mapping up so that they are all mapped to the same drive
and lastly the linked table needs to be saved like the one before, to ensure that it has the correct mapping to the drive on the server.

Hope this helps\works

 

[firemark1817]

Try using UNC link

Try changing the linked table to use the Universal Naming Convention such as:

\\servername\path\file.mdb

This eliminates the problem of having the path mapped to different drives.

 

[Andrew Fergus]

Thanks for the suggestions everyone. In answer to some of the questions :

No-one is changing the table structures in the linked databases - it is just a case of more data being added to the existing tables.

The drive mappings & re-connections are not a problem - the names and letters are 100% stable and do not change plus I regularly open files across the network (from the same source) throughout the day.

I think I have found the source of the problem - Access is using the correct convention of \\server\drive\path\path\file.mdb but it is also inserting a drive letter reference like this \\server\drive (E)\path\file.mdb and I think it is the (E) bit that is causing the problem. "E" is the letter of the drive but the drive is actually called "datadisk" so it appears as ...\datadisk (E)\.... If I try to change this in the design of the linked table (under View -> Properties), Access keeps putting it back to \\server\drive (E)\path etc. So I can't change it or save the change. If I re-link the table I don't get an option to edit the server or filename path.

How do I force the linked table to drop the (E)? Or how do I change the UNC path for a linked table? (version : Access 2000)

Thanks again
Andrew

 

[tails]

With the (E) at that bit in the mapping, just type in the save box as \\servername\folder\folder\filename.mdb dont put in the drive letter mapping, it shouldn't be nessecary. As all that is, is the server name itself. so by putting in \\servername\drive\folder\folder\filename you are actually telling it to map to \\servername\servername\folder\folder\filename.mdb. when you map a drive you map it to that certain server and so you shouldn't be putting in the drive at all. thats why it is automatically putting in the (E) too i think. Hope this helps

 

[Andrew Fergus]

Thanks for the response. Where exactly do I do the "save as"? I can't see this option and I can't overwrite the default value in the table properties.
Andrew

 

[tails]

well when you link the other table to the one you are creating it asks for where the file is situated, when you get to that screen you dont click on browse and search for the file itself. You should just type the path in the box and then click ok and it should work fine (i hope). make sure you map it to \\sername\folder\folder\filename.mdb so that it doesn't put in the drive letter too. I hope you can understand this coz im finding it hard to explain without being there to do it... lol, well hopefully it should work.