Users accessing same table

[pkeegs]

I have an Access 2003 database on a small network (server + 4 pc's) with the
database split and each PC having its own front end. Recently the users have
reported not being able to access the same form at the same time, they have
to wait until the other user has finished and closed it before accessing it.
Any suggestions regarding this problem?

 

[Daniel Pineault]

What error message are they getting?

They should have no issue opening the same form, but there could be a lock
conflict if they try to access the same record at the same time. Please post
some more information (the exact error) so we can try to guide you better.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[pkeegs]

I have worked through with the staff that have been having the problem but
they did not seem to be able replicate it. I did go through all the database
files & "compact and repair" which has not been carried out for quite some
time. Files dropped from 5.51mb to 2.85mb on the front end and 34.5mb to
1.48mb on the back end! Could that have been something to do with the
problem? Is it a problem to have "Compact on Close" (Tools Option)
permanently selected?

 

[Steve]

< Is it a problem ........ >

No! It would also be wise to have an automatic backup.

Steve
(e-mail address removed)

 

[Larry Linson]

Compact on Close only compacts/repairs tables in the database where it
resides, so if you add it to the front end, it will only compact local
tables in the user's copy of the front end. If you put it in the back end,
effects will be unpredictable, as it can only take effect when the database
in which it resides has no other users logged in.

Neither of the sizes you quote is exceptionally large for a Jet or ACE
database, but the compression does indicate there was a lot of
no-longer-used storage, and there's always the possibility of something
going wrong when there is "trash" lying around.

If you have a design such that there are no tables updated by the user in
the front end, then you could just download a fresh copy of the frontend
from a central repository rather than use Compact. That is the best way to
design your database application, in any case... if there are "per user"
values that need to be saved, they can be saved in the common back end in a
table that includes the user's id to identify them; if they are "per user
machine" rather than "per user", they can be in another set of linked tables
that are stored on the local machine. (That complicates re-linking tables on
startup, but doesn't make it impractical -- but it is an uusual situation.)

Larry Linson
Microsoft Office Access MVP