Prioritize Objects for Speed

G

Gene

I use a Win 2000 server with 4 XP Pro computers hardwired to it. The main
data database is located on the server (I am sorry, I do not know which you
consider front/back end). All users access the database on the server with
the same enduser Access program. On each of our machines, we access the
server database by opening Tables, Querries, Forms, Reports, and via VBA,
virtual files. Sometimes accessing the data on the server takes a very long
time. My suspicion is that one of the machines has an object open that is
locking up the availability of the database on the server. Sometimes when I
close a table on one machine, all the other machines get their requests met
immediately but that may just be coincidental. Is there a priority to these
objects in which occupy more of the server database time? If I don't close a
virtual file, does that lock up the data longer than having a form open? If
I want to address a Table, is it worse to directly open that table and leave
it open, or should I create a querry or form and use them instead. Probably
more important, is there a good book that would address some of these issues?
Thanks. Most of this database has been created with the help of this
newsgroup!
Gene
 
W

Wayne Morgan

The back-end should be on the server. It should be a shared file that has
the tables for the database, nothing else. The front-end should have the
forms, queries, VBA code, etc. Each user should have their own copy of this.
The front-end would have linked tables to the back-end file. Any temp table
that you use should also be handled by the front-end. It is actually best to
put them in a separate file than the front-end, but the separate file should
still be a file on the user's computer. The reason for the separate file is
to reduce causing the front-end to bloat in size.

For information on splitting a database and handling temp tables, check
these links on Tony's site. He also has some information on improving
performance.

http://www.granite.ab.ca/access/splitapp/index.htm
http://www.granite.ab.ca/access/performancefaq.htm
http://www.granite.ab.ca/access/bloatfe.htm
 
Top