Linked Tables - path or current folder

[Rx_]

My Access Application (front-end application) is linked to a _be (back-end data tables) in a big company on a Windows 2003 server using Citrix for the users to start an Access 2007 application.

The Application and _Be are in the same folder on Windows 2003 server.
The path for the ODBC linked table is about six sub directories.
1. Is there code to link to "current directory"? (i.e. \\currentdirectory\production.mdb
2. Could this in any way be more efficient than a fully qualified path?
i,e. X:\sys\office\state\city\group\div\mgr\application\production.mdb

Problem: Parts of the day, the application is lighting fast. My error traps are clean. When the network (or what ever) gets busy, my error traps (thanks to a error log) start to get real busy.
A form with lots of business rules, that uses a half dozen small table fetches and one post goes from about 200 ms to 8 seconds.

This happens from my development workstation to the Windows 2003 server and also from Citrix running the Application and _BE in the same folder.

IT is watching network traffic, server usage that do seem to have an impact on the timing.
The Linked Table is just one more thing that we want to try if it is worth the time.

 

[The_Doc_Man]

Unless there is a specialized "mount point" that contains some part of the path (and looks like a separate drive letter), using a fully qualified path is necessary because of the file system security that will be required. You must undergo security arbitration for every folder in the entire path every time you open a file.

Are you aware of the ".Connect" property of a linked table? It should contain the correct path if your tables are correctly linked. You could then parse that string by finding the LAST "/" and marking that spot. It is the end of the device/path portion of the sequence. Everything to the right of that last slash is file name and type.

CurrentDB.Name tells you the fully qualified device, path, name, and type of the database file that is DBEngine.Workspaces(0).Databases(0) - does that help?

 

[Rx_]

Excellent Response, very understandable and it totally makes sence.
Thank you for a great answer(s).

Given this, let me ask part two of the question. Lets assume for a moment that the network could be the problem adding delays throughout the day:

On my development workstation, the linked drive to the _BE folder on the server is: X:\sys\office\state\city\group\div\mgr\application \production_BE.mdb
(A Networked path - this is the citrix server for users to access)

Once the Access Appication is ready for release and posted on the citrix server:
Is there an advantage to use linked table manager to change the path from:
(Mapped Network drive, even though it is on the same physical server) X:\sys\office\state\city\group\div\mgr\application \production_BE.mdb
to a local server drive path:
D:\sys\office\state\city\group\div\mgr\application \production_BE.mdb

The question is: Could a linked table call to a server's local hard drive avoid some network overhead of calling a mapped network drive that points to the exact same local hard drive?

 

[gemma-the-husky]

out of interest - read ms knowledge base article 889588 for a number of tips to speed up access.

one of these is to not use long file names (stick with 8.3), and to keep the back end as near to the top level as possible. ie yours is 9 levels down the folder chain.

 

[Rx_]

Thanks!
This iwll be great information to present to the IT department.
Sometimes, it is the little things that take up all the time.

 

[The_Doc_Man]

There is also the practical side of this. Some fields cannot store more than 255 characters. With a long path and a stickler of an IT department, you can run into some really serious string sizes that cannot be navigated so easily. Or at all.

 

[Rx_]

A quick followup:

The Windows 2003 server that runs and holds Access Files.
The IT manager turned off the Live Virus Scan.
The random delays decreased.

Submitting a complex set of data and having the form complete its events and formatting changed from:
Six seconds to a lost link (randomly)
To under two seconds.

The same code is performing consistentlly and the error log is staying empty. Before, the events might not ever fire, or fire in order.

Thanks for all the valuable input.
Only after eliminating many possibilities will the solution be evident.

http://support.microsoft.com/kb/889588
Edited: this is the article mentioned above and below

 

[gemma-the-husky]

a couple of other likely things

open a hidden form, or recordset when you open the database, and keep it open until you close the dbs. Maintaining a link makes a lot of difference

add the sharing violation notification key, recommended in the MS KB article I referred to above.