"Karen" wrote
The forms and reports all run fine, but
the database bloats unbelievably daily.
I tried creating a backend database and
frontend mde files to try to control both
the bloat and the conflicts between users,
but found these to be slower than the
database as a whole. It did stop the
conflicts and lock outs, but the forms
and reports take minutes instead of
seconds to load.
To my question, is there a service or
individual that will look at my current
database and make suggestions on how
I can make it run more efficiently? Is
there a database example close to what
I have created that I can examine to
see what was done differently?
The best collection of information on multiuser performance and preventing
corruption is at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm. For my general overview of multiuser
(at least indicating topics I thought worth discussing with my user group)
download my presentation on _Access in a multiuser environment_ from
http://appdevissues.tripod.com.
Bloat is due to Access not recovering vacated space until a compact is done,
or from using OLE Objects to store images. If you create temporary tables in
your database and then delete them, you can avoid that bloat by first
creating a temporary database, creating the temp tables in the separate
database, and then, when done with them, delete the entire temp database --
you'll find an example of that, too, at Tony's site.
Another key item, in my experience, is to limit as much as possible the data
brought back across the network. Don't open a Form or Report on a Table or
entire Query unless you _need_ every record -- instead use a Query that
includes a WHERE clause on an indexed field, and you'll need to bring across
only the index to determine the particular records needed. It is amazing how
many times you only need to retrieve one whole record (if it exists) or none
(if it doesn't).
There are other settings and tricks... "OpLocks" and "keeping a connection
open" are two examples... Tony's site has them. As you continue to run into
rough spots, keep coming back and asking specific questions.
Now, as to finding someone to do paying work -- you will probably have
already had e-mailed offers from lurkers here, but the long-time,
experienced participants know "this isn't the place for that" and many will
not do so. See the FAQ posted each day or the FAQ site,
http://www.mvps.org/access/netiquette.htm to verify, and for other good
suggestions on effective use of newsgroups. You will find this a helpful
place, on the whole. (Just look out for a fake MVP suggesting you go to the
singular form of the base URL of the hyperlink above -- it's a trap, with a
chain of popups. He likes to tag on to our posts and try to discredit the
MVP program.)
Larry Linson
Microsoft Access MVP
