Linked Tables (2003 11.5614.5703)

Topic

New Reply

I’m working on a database with linked tables (front end – back end). When my linked tables are on my local hard drive things work fine. When I move them to a network drive and relink with the Linked Table Manager, things REALLY slow down. Even just opening forms in design mode (seemingly not needing the tables), editing them, and saving them takes too long (e.g., 15-20 seconds to open or save). Again, things work fine when the linked tables are local. I don’t think there’s a problem with the network or the network drive — moving/copying files to and from there works quickly. I’ve tried the suggestions provided here but they don’t seem to offer any significant improvement.

If someone else is accessing the linked tables at the same time using their own copy of the front end, things grind to a virtual halt (the opening and closing mentioned above takes several minutes). I’m guessing I’m completely overlooking some no-brainer “law” of shared databases, but this brain is at a loss at the moment. Are there some “usual suspects” I should be dealing with? Do options like refresh intervals make a difference? What are the pros/cons of the various record locking options?

Any suggestions would be greatly appreciated!

Thanks.

Reply | Quote

Replies

This sounds much like the locked LDB file issue where Access tries to delete the LDB file for the database a number of times before deciding it’s the existing one should be used, but if you’ve got a form open that links to a simple table, then that shouldn’t be a problem. What is the performance like just using the database, rather then trying to make design changes?

Reply | Quote

Wendell,.

Thanks for your response. The performance is equally sluggish when I’m “just using the database.” It’s almost tolerable if I’m the only one accessing the tables, but it really goes south if someone else has their front-end running and is linking to the back-end.

When you say “if you’ve got a form open that links to a simple table, then that [the locked LDB file issue] shouldn’t be a problem”, do you mean employing the “trick” of opening a form that links to a back-end table at the outset and then leave it open (albeit hidden) throughout the session? If so, I am doing this EXCEPT that the form does not link to a back-end table. Is that what I’m missing? Easy enough to add and try it, but I’ll have to wait until I’m back at work on Monday where the network resides (it’s late now but I’ll try it on my little home network tomorrow to see if there’s a performance improvement).

Thanks again.

Reply | Quote

Wendell indeed means a form bound to a linked table in the backend; if the form is opened when the database is opened and closed when the database is closed, the .ldb file will remain in existence during the entire session. Opening a form that is not bound to a linked table in the backend will not have this effect.

Another way to do this would be to open a recordset based on a backend table and keep it open during the entire session.

Reply | Quote

Hans,

Thanks for the clarification. I’ll give it a shot!

Reply | Quote

Hans,

Thanks for the clarification. I’ll give it a shot!

Reply | Quote

Wendell indeed means a form bound to a linked table in the backend; if the form is opened when the database is opened and closed when the database is closed, the .ldb file will remain in existence during the entire session. Opening a form that is not bound to a linked table in the backend will not have this effect.

Another way to do this would be to open a recordset based on a backend table and keep it open during the entire session.

Reply | Quote

Hans hit the nail on the head – the table you connect to, whether by a hidden form, or by opening a recordset, should be in the back-end. The idea is to force the creation of a lock (.ldb) file for the back-end. Otherwise, each time you open a table, or a form or report bound to a table, Access first checks to see if there is a lock file, and if there is, it tries several times at 1 second intervals (more or less) to delete it. If it’s successful, it goes ahead and creates a new one, if not it uses the existing one.

Reply | Quote

Wendell,

Thanks for the help. I’ll let you know how it works out.

Reply | Quote

Wendell,

Thanks for the help. I’ll let you know how it works out.

Reply | Quote

Hans hit the nail on the head – the table you connect to, whether by a hidden form, or by opening a recordset, should be in the back-end. The idea is to force the creation of a lock (.ldb) file for the back-end. Otherwise, each time you open a table, or a form or report bound to a table, Access first checks to see if there is a lock file, and if there is, it tries several times at 1 second intervals (more or less) to delete it. If it’s successful, it goes ahead and creates a new one, if not it uses the existing one.

Reply | Quote

Wendell,.

Thanks for your response. The performance is equally sluggish when I’m “just using the database.” It’s almost tolerable if I’m the only one accessing the tables, but it really goes south if someone else has their front-end running and is linking to the back-end.

When you say “if you’ve got a form open that links to a simple table, then that [the locked LDB file issue] shouldn’t be a problem”, do you mean employing the “trick” of opening a form that links to a back-end table at the outset and then leave it open (albeit hidden) throughout the session? If so, I am doing this EXCEPT that the form does not link to a back-end table. Is that what I’m missing? Easy enough to add and try it, but I’ll have to wait until I’m back at work on Monday where the network resides (it’s late now but I’ll try it on my little home network tomorrow to see if there’s a performance improvement).

Thanks again.

Reply | Quote

This sounds much like the locked LDB file issue where Access tries to delete the LDB file for the database a number of times before deciding it’s the existing one should be used, but if you’ve got a form open that links to a simple table, then that shouldn’t be a problem. What is the performance like just using the database, rather then trying to make design changes?

Reply | Quote