MS Access 'already opened exclusively' error

  • Thread starter Thread starter Jared
  • Start date Start date
J

Jared

I have three machines running the same C# app, all accessing the same
MDB file, which is shared on one of the machines. All machines have
the same version of MDAC (2.8). The database does not use Access
multi-user security, but is password-protected. Each machine connects
to the database on a regular basis (i.e. about once each minute on
average), and performs more reads than writes. The app is written in
such a way that all connections are closed properly, and I'm using JRO
to compact/repair the MDB daily.

I occasionally get the following error message: "You attempted to open
a database that is already opened exclusively by user 'Admin' on
machine '(machine name)'. Try again when the database is available."

I've been logging these errors, along with the calling method
information. The errors are not associated with a single method, and
do not seem to be caused by any specific thing that my app is doing.

I've heard people complain that Access is buggy, but I've used it a lot
over the years with much more demanding apps and never had this kind of
problem. I had originally considered using MSDE, but decided on Access
because a file-based database is easier to apply structure changes to
(i.e. make a copy, update original via DDL, replace with the copy to
rollback if necessary) and is easier to back up (i.e. just FTP the MDB
file to a remote site). MSDE is also more problematic to install on a
target machine.

Since I'm not using MS Access to open the file, this error appears to
be incorrect. Does anyone have any experience finding the true source
of this error, or with troubleshooting problems with Access in general?
 
I have three machines running the same C# app, all accessing the same
MDB file, which is shared on one of the machines. All machines have
the same version of MDAC (2.8). The database does not use Access
multi-user security, but is password-protected. Each machine connects
to the database on a regular basis (i.e. about once each minute on
average), and performs more reads than writes. The app is written in
such a way that all connections are closed properly, and I'm using JRO
to compact/repair the MDB daily.

I occasionally get the following error message: "You attempted to open
a database that is already opened exclusively by user 'Admin' on
machine '(machine name)'. Try again when the database is available."

I've been logging these errors, along with the calling method
information. The errors are not associated with a single method, and
do not seem to be caused by any specific thing that my app is doing.

I've heard people complain that Access is buggy, but I've used it a lot
over the years with much more demanding apps and never had this kind of
problem. I had originally considered using MSDE, but decided on Access
because a file-based database is easier to apply structure changes to
(i.e. make a copy, update original via DDL, replace with the copy to
rollback if necessary) and is easier to back up (i.e. just FTP the MDB
file to a remote site). MSDE is also more problematic to install on a
target machine.

Since I'm not using MS Access to open the file, this error appears to
be incorrect. Does anyone have any experience finding the true source
of this error, or with troubleshooting problems with Access in general?
Click to expand...

This is a shot in the dark...

Do you have the database password protected?

If you don't, you may have a user who is opening it with Access and using it in
a way that would cause locking.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
Also make sure that the database's properties allow
multiple users. In Access2003, it's under the Advanced
tab, Default open mode. I also had default record locking
set to No Locks, and the checkbox that says "Open databases
using record-level locking" checked.

Hope that helps.
Robin S.
-----------------------
 
¤ I have three machines running the same C# app, all accessing the same
¤ MDB file, which is shared on one of the machines. All machines have
¤ the same version of MDAC (2.8). The database does not use Access
¤ multi-user security, but is password-protected. Each machine connects
¤ to the database on a regular basis (i.e. about once each minute on
¤ average), and performs more reads than writes. The app is written in
¤ such a way that all connections are closed properly, and I'm using JRO
¤ to compact/repair the MDB daily.
¤
¤ I occasionally get the following error message: "You attempted to open
¤ a database that is already opened exclusively by user 'Admin' on
¤ machine '(machine name)'. Try again when the database is available."
¤
¤ I've been logging these errors, along with the calling method
¤ information. The errors are not associated with a single method, and
¤ do not seem to be caused by any specific thing that my app is doing.
¤
¤ I've heard people complain that Access is buggy, but I've used it a lot
¤ over the years with much more demanding apps and never had this kind of
¤ problem. I had originally considered using MSDE, but decided on Access
¤ because a file-based database is easier to apply structure changes to
¤ (i.e. make a copy, update original via DDL, replace with the copy to
¤ rollback if necessary) and is easier to back up (i.e. just FTP the MDB
¤ file to a remote site). MSDE is also more problematic to install on a
¤ target machine.
¤
¤ Since I'm not using MS Access to open the file, this error appears to
¤ be incorrect. Does anyone have any experience finding the true source
¤ of this error, or with troubleshooting problems with Access in general?

There are a number of issues that can cause this problem, from a corrupt database to a bad .LDB
file. I would take a look at the below article. Maintenance for an Access database is important,
especially in a network environment where the resource is shared.

How to keep a Jet 4.0 database in top working condition in Access 2000
http://support.microsoft.com/kb/300216


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Do you have the database password protected?
If you don't, you may have a user who is opening it with Access and using it in
a way that would cause locking.
Click to expand...

The database is password-protected using a strong password. While it's
easy to crack Access passwords, it's my understanding that these
customers are not that sophisticated, nor would they have reason to
bypass the app and go to the database directly.
 
RobinS said:
Also make sure that the database's properties allow
multiple users. In Access2003, it's under the Advanced
tab, Default open mode. I also had default record locking
set to No Locks, and the checkbox that says "Open databases
using record-level locking" checked.
Click to expand...

I could be wrong, but I think that setting is only relevant when using
MS Access to open a database. I'm using a C# app and ADO.NET to access
the database, and I'm pretty sure MS Access isn't installed on any
machines at this customer's location. My connection string doesn't
specify any locking.
 
Jared said:
I could be wrong, but I think that setting is only relevant when using
MS Access to open a database. I'm using a C# app and ADO.NET to
access
the database, and I'm pretty sure MS Access isn't installed on any
machines at this customer's location. My connection string doesn't
specify any locking.
Click to expand...

Well, your choice. I had a VB app with 50 users accessing
my Access database, and those were my settings. It doesn't
cost you anything to try it, but you are certainly not
required to take any advice that people offer you in
this newsgroup. :-)

Robin S.
 
Paul said:
There are a number of issues that can cause this problem, from a corrupt database to a bad .LDB
file. I would take a look at the below article. Maintenance for an Access database is important,
especially in a network environment where the resource is shared.

How to keep a Jet 4.0 database in top working condition in Access 2000
http://support.microsoft.com/kb/300216
Click to expand...

Thanks for the link, Paul. Our app compacts and repairs the database
via JRO before transferring a backup to an offsite server via FTP
nightly. We're primarily seeing this error with customers who use our
app in a networked environment, so I'm suspecting that there are some
network reliability issues involved.

We have tons of experience with SQL Server, but none with the desktop
version. I've looked into using MSDE, but deploying it is *much* more
difficult than adding an MDB file to our installer. Not to mention
that it would inflate our installer from 5MB to 40MB. When you're
trying to get customers by offering an anonymous free trial of your
software, this isn't a good thing. =)

Jared
 
RobinS said:
Well, your choice. I had a VB app with 50 users accessing
my Access database, and those were my settings. It doesn't
cost you anything to try it, but you are certainly not
required to take any advice that people offer you in
this newsgroup. :-)
Click to expand...

I sure didn't mean to offend you, and I *do* appreciate all advice I
get on the groups. I'll be the first to admit that I'm not an expert
on all the mysterious nuances of MS Access. Do you have information
showing that this setting is relevant when not using MS Access to open
the database, or are you guessing that it's relevant?

I guess the whole subject is irrelevant anyway because those are the MS
Access defaults, and my settings are already exactly as you described.
=)

Jared
 
Jared said:
I sure didn't mean to offend you, and I *do* appreciate all advice I
get on the groups. I'll be the first to admit that I'm not an expert
on all the mysterious nuances of MS Access. Do you have information
showing that this setting is relevant when not using MS Access to open
the database, or are you guessing that it's relevant?

I guess the whole subject is irrelevant anyway because those are the
MS
Access defaults, and my settings are already exactly as you described.
=)

Jared
Click to expand...

No, I wasn't offended at all. It was just an idea I had. I've
seen the error you're having, and was wracking my brain to
try to remember how it got resolved. Bummer it didn't work.

If you have the Access database on a network share, you might
just check and make sure people have *write* access to the
share. When you open the database from a VB app, it *does*
still log people in to it (as admin, unless you're putting
their username and pwd into your connection string), and if
they don't have write access to the directory where the database
is, they won't be able to open it, because it won't be able
to create the ldb (lock) file.

If I think of something else, I'll let you know.

Robin S.
 
RobinS said:
No, I wasn't offended at all. It was just an idea I had. I've
seen the error you're having, and was wracking my brain to
try to remember how it got resolved. Bummer it didn't work.

If you have the Access database on a network share, you might
just check and make sure people have *write* access to the
share. When you open the database from a VB app, it *does*
still log people in to it (as admin, unless you're putting
their username and pwd into your connection string), and if
they don't have write access to the directory where the database
is, they won't be able to open it, because it won't be able
to create the ldb (lock) file.
Click to expand...

Good suggestions. At startup, our app attempts a database write
operation to verify connectivity, permissions, etc.

As it turns out, this customer was having power issues on their server
causing it to intermittently shut off. Something (almost certainly the
power issue) caused the database to corrupt. Specifically, a primary
key index was converted to a non-unique index, and the table was
reseeded to 1 (thanks MS Access!). I spent about four hours this
weekend investigating the problem, determining the cause, and writing
some DDL that their system could retrieve and execute against the
database to update the bad identity values and recreate the primary key
field.

I'd like to talk to the MS developer who built the "silently remove
primary key index and reset seed value to 1" feature in the Jet
compact/repair routine. =)

Jared
 
Jared said:
Good suggestions. At startup, our app attempts a database write
operation to verify connectivity, permissions, etc.

As it turns out, this customer was having power issues on their server
causing it to intermittently shut off. Something (almost certainly
the
power issue) caused the database to corrupt. Specifically, a primary
key index was converted to a non-unique index, and the table was
reseeded to 1 (thanks MS Access!). I spent about four hours this
weekend investigating the problem, determining the cause, and writing
some DDL that their system could retrieve and execute against the
database to update the bad identity values and recreate the primary
key
field.

I'd like to talk to the MS developer who built the "silently remove
primary key index and reset seed value to 1" feature in the Jet
compact/repair routine. =)

Jared
Click to expand...

Wow, that's a *really* special, uh, "feature". (MS products don't
have bugs, they have undocumented features.) Congrats on figuring
out what happened and why.

Robin S.
 
Back
Top