checking for linked tables

[dpimental]

Here's a stupid question. If I have linked tables in my frontend access db application, what do I need to check for on start up?

Do I need to see if they are still there / linked?

Is it possible that they are linked but somehow corrupted?

What sorts of problems can happen with linked tables.

Here's the scenario. The Backend db and the frontend db are in the same folder on a network drive. The frontend is a non-exclusive (i.e. shared) db.

David Pimental
(US, Oh)

 

[maxhugen]

There are no stupid questions, only stupid answers

Links in Access are very stable. You should not need to check anything on startup.

Having only the tables in a BE usually makes this very stable too, another good reason to split a db into FE/BE.

The only issue that I can think of for links to fail is if the BE has been moved, or the mapping to the network drive has been changed, so that the FE can't find the db with the linked tables.

Max Hugen
Australia

 

[JerryKlmns]

To get rid of the mapping letter threat, link your tables using \\server\folder\subfolder\database.mdb
The only problem that might occur is loosing network connections. Very often. So if you care for start to check that do a Dir("\\server\folder\subfolder\database.mdb") and catch any errors.

But if your users open the FE residing on the server you don't have any gain in network traffic. Make a copy of the FE to every user 's PC and make them use that.

 

[dpimental]

On the project that I am working on, we are placing the backend and the frontend databases on the same shared network drive. I use the CurrentProject.Path to determine the path of the linked tables along with the name of the backend database, since they are in the same directory.

One question is, if it doesn't find the linked table, what does it do? What errror does it throw? Does it just not show the linked table or does it show it ; but then throw an error when you try to use it?

David Pimental
(US, Oh)

 

[maxhugen]

G'day David

The linked table will still show up in the Database window. If you try to open it, you get a message "...cannot find the input table or query..."

In my applications, when a user tries to Logon, a function first runs to ensure the BE can be found, using Dir(). If not, a window opens asking the user to enter the db location - I add a button next to the location textbox that allows them to access the Windows 'Browse' screen. Then I relink each table.



Max Hugen
Australia

 

[JerryKlmns]

David,

If you also use an .mdw for user level security, which propably resides on the same folder of the BE, then this

Left(SysCmd(acSysCmdGetWorkgroupFile), InStrRev(SysCmd(acSysCmdGetWorkgroupFile), "\"))

returns the full path to your mdw & BE. If the only reason to leave the FE on the server is that, you could change it!

 

[MajP]

There is a lot of code out there for checking for linked tables, and reconnecting if needed. Links do not usually drop, but they do :

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

The Access Developers Handbook has a very robust set of procedures.

As Jerry pointed out you need to use the absolute reference for the folder. To do this in the update wizard you have to input this by hand, do not click on the tables.