Problems accessing Access database when more than one user is involved!

[Andrew Wiseman]

I have set up a simple (unsplit) Access 2000 database with a single table
whose records are editable in a form. I observe the following behaviour when
two instances of Access 2003 are attempting to use the same database (that
is whether two users are accessing the MDB file from a network share or the
application and *.MDB file is on a local machine with a single user running
two instances of Access)...

1. If two instances are running and a record is edited (using the form) in
one of the instances, when the user attempts to save the change, he gets the
message: "Microsoft Access can't save design changes or save to anew
database object because another user has the file open. To save your design
changes or save to a new object, you must have exclusive access to the
file." This happens even if the two instances are pointing at different
records.

2. If only one instance is running, a user is able to save changes to a
record in the form. But having done so, attempting to run a second instance
of the database fails, with the message: "The database has been placed in a
state by user 'Admin' on machine 'XXX' that prevents it from being opened or
locked."

If I look in Tools Options Advanced in MS Access, I see the default open
mode is shared, the default record locking is set to "No locks" and the
"Open databases using record-level locking" is ticked.

I seem to be having exactly the same problem as the guy in this forum
describes:
http://www.duxcw.com/yabbse/index.php?board=10;action=display;threadid=15035

Anyone know the quickest way of solving this problem? (I'm looking for a
lazy option that doesn't involve splitting databases, compiling front ends
or adding user accounts and security!)

 

[Jeff Boyce]

If your database is not split, and if not, your users are "sharing" a single
..MDB file, located on your network, I'm guessing you've been very lucky not
to have to deal with a corrupted database.

If someone has the (single) file open, why would you want Access to allow
someone else to make a change? That would mean that person 1 has opened
something that is no longer the same or even valid.

You may not wish to have a suggestion involving splitting your database ...
but that doesn't mean that not splitting it is a wise idea.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Andrew Wiseman]

If your database is not split, and if not, your users are "sharing" a
single .MDB file, located on your network, I'm guessing you've been very
lucky not to have to deal with a corrupted database.

It seems to me Access 2003 is doing its very best to prevent the database
from being corrupted, which is why the users can't seem to use it properly
at the same time.

You may not wish to have a suggestion involving splitting your database
... but that doesn't mean that not splitting it is a wise idea.

I guess when enough people tell me to split the database, I have to realise
that I might be in the wrong for being stubborn! So I've bitten the bullet
and split my database, making a copy of the front end. Now when I access the
database using both front end copies at the same time, the problems I had
before disappear.

Thanks for being patient with me

Andrew