Linking tables across servers

[kev747]

Hi All.

Im a bit of newbie when it comes to this aspect of Access, so I hope this makes sense.

I have a database that is located in a groups directory on my server. However, this database will now need to be accessible to a larger number of users across the country, who are located on different servers.

When I create a back-end for the database, the linking pathway is displayed in the table properties as
DATABASE=K:\MyFolder\SubFolder\MyDatabase\MyDatabase_be.mdb;TABLE=tblMyTable

This works fine when users on the same server access the database, but if someone else on a different server maps to the drive, the link doesn't seem to work ( I think it's because of the drive letter association with the server).

Does anyone have a fix I might be able to put in place so that the linking pathway shows the server, rather than the drive letter, or is there a better way I could fix this problem?

Thank in advance for any suggestions forthcoming.

Kev.



This is a politically correct signature, due to constant whinging about my previous signature.
Long live the fun police.

 

[Crowley16]

assuming the server is accessible, then you can use code to delete and re-link the tables with a unc path

'delete linked table first
DoCmd.DeleteObject acTable, tblNme

're-link the table
DoCmd.TransferDatabase acLink, "Microsoft Access", "unc path", acTable, tblNme, tblNme

--------------------
Procrastinate Now!

 

[kev747]

Thanks for the reply Crowley16.

Like I say, Im a bit of a dope when it come to this so I hope I don't sound stupid.

Where would I put this code? I'm assuming in the "On Open" event of of my main form. I tried this but to no avail. Below is the code I used -

DoCmd.DeleteObject acTable, tblEMITEquipment, tblCopier, tblFax, tblLaptop, tblPC, tblPrinter, tblScanner, tblMFD, tblMonitor, tblTelephone

DoCmd.TransferDatabase acLink, "Microsoft Access", ":\\sydab1fs16-adm\Usr\Users\EM IT Equipment_be", acTable, tblCopier, tblFax, tblLaptop, tblPC, tblPrinter, tblScanner, tblMFD, tblMonitor, tblTelephone

When I open the form I get a 'Compile Error - Variable not defined' message indicating a problem with the first table listed in the DeleteObject command.

Am I doing it all wrong?

Kev.


This is a politically correct signature, due to constant whinging about my previous signature.
Long live the fun police.

 

[PHV]

Have a look here:

http://www.mvps.org/access/tables/tbl0009.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kev747]

Hey PHV.

Thanks for the link to that code.

Where would I put it exactly?

Kev.

This is a politically correct signature, due to constant whinging about my previous signature.
Long live the fun police.