Unable to compact/repair database

[John]

Hi. Sometimes when I go into an access database that I wish to shrink
in size to reduce old records, I go to the Tools menu and select
Database Utilities and Compact & Repair Database. I often get an error
message though saying that I am already in it? What gives?

The message in more detail says:

"You attempted to open a database that is already opened exclusively
by user 'admin' on machine 'Nameofmachine'. Try again when the
database is available".

I don't understand what I have done wrong or why it often comes up
with this message. Microsoft Access seems to be very temperamental
when it comes to compacting this database I use.

When I tried to compact this I had made sure that no other users where
in it, either I had asked them to exit it until I had compacted it, or
I had done it first thing in the morning or last thing before I went
home but after everyone else had gone. So I am not sure why it still
gives this error message?

Can anyone shed any light on this and tell me what the issue might be
and how I can get it compacted without any problems? It seems to only
work once in a while and I have not been able to understand why.

Cheers

John

 

[Joseph Meehan]

Hi. Sometimes when I go into an access database that I wish to shrink
in size to reduce old records, I go to the Tools menu and select
Database Utilities and Compact & Repair Database. I often get an error
message though saying that I am already in it? What gives?

The message in more detail says:

"You attempted to open a database that is already opened exclusively
by user 'admin' on machine 'Nameofmachine'. Try again when the
database is available".

I don't understand what I have done wrong or why it often comes up
with this message. Microsoft Access seems to be very temperamental
when it comes to compacting this database I use.

When I tried to compact this I had made sure that no other users where
in it, either I had asked them to exit it until I had compacted it, or
I had done it first thing in the morning or last thing before I went
home but after everyone else had gone. So I am not sure why it still
gives this error message?

Can anyone shed any light on this and tell me what the issue might be
and how I can get it compacted without any problems? It seems to only
work once in a while and I have not been able to understand why.

Cheers

John

Is it a "shared" database? Someone else may have it open. Another
possibility is it did not close down properly last time it was used, maybe
turned off the computer while it was still open. In those cases you should
see a YOURFILENAME.LDB file still there. Exiting the database then deleting
that file should clear it up. If it is shared then you need to get them to
exit.

 

[Duane Arnold]

Hi. Sometimes when I go into an access database that I wish to shrink
in size to reduce old records, I go to the Tools menu and select
Database Utilities and Compact & Repair Database. I often get an error
message though saying that I am already in it? What gives?

The message in more detail says:

"You attempted to open a database that is already opened exclusively
by user 'admin' on machine 'Nameofmachine'. Try again when the
database is available".

I don't understand what I have done wrong or why it often comes up
with this message. Microsoft Access seems to be very temperamental
when it comes to compacting this database I use.

When I tried to compact this I had made sure that no other users where
in it, either I had asked them to exit it until I had compacted it, or
I had done it first thing in the morning or last thing before I went
home but after everyone else had gone. So I am not sure why it still
gives this error message?

Can anyone shed any light on this and tell me what the issue might be
and how I can get it compacted without any problems? It seems to only
work once in a while and I have not been able to understand why.

Here is my opinion on the whole thing. No application that is a multi
user application should be using Access as a database. The application
should be using MS SQL Server as the database, which is designed for
multi user usage. Access is not designed for multi user usage, although
people try to do it.

Duane

 

[John]

Is it a "shared" database? Someone else may have it open. Another
possibility is it did not close down properly last time it was used, maybe
turned off the computer while it was still open. In those cases you should
see a YOURFILENAME.LDB file still there. Exiting the database then deleting
that file should clear it up. If it is shared then you need to get them to
exit.

Thanks. Yes it is a shared database.

John

 

[John]

Here is my opinion on the whole thing. No application that is a multi
user application should be using Access as a database. The application
should be using MS SQL Server as the database, which is designed for
multi user usage. Access is not designed for multi user usage, although
people try to do it.

I definitely agree with you on that! Unfortunately the company I work
for despite being extremely large and having lots of money, doesn't
seem to be too bothered about making something more usable. I don't
think it is very high on their list of priorities. As long as what we
have gets the job done, no matter how long it takes they're not
interested in improving it.

John

 

[Guest]

Gee Duane,

I sure wish I had taken your advice to heart before implementing SEVERAL
very successful Access applications that are multiuser, or before writing
this article:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Here is my opinion on the whole thing. No application that is a multi
user application should be using Access as a database. The application
should be using MS SQL Server as the database, which is designed for
multi user usage. Access is not designed for multi user usage, although
people try to do it.

Duane

 

[Guest]

Using Windows Explorer, and with everyone out of the database (including
you), do you see a locking database file (*.ldb)? Two causes include 1.)
improper exiting, as Joseph suggested, or 2.) your database has some
corruption.

Is your database split into two .mdb files: a front-end (FE) and back-end
(BE)? If the answer is yes, does each user have their own copy of the FE
installed on their local hard drive?

Do you have any start-up forms/code in this database? When you are sure that
everyone else is out, try opening it while holding down the shift key. Keep
the shift key pressed down until the database is completely open. This will
allow you to bypass a form designated as a startup form (under Tools >
Startup), along with any macros named either autoexec and/or autokeys. This
will work as long as the shift key trick has not been previously disabled.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

PS.
The only way I would agree with your statement is if:

1.) You are using a WAN (wide area network) versus a LAN (local area
network), although Terminal Server can still be quite useful for a limited
number of clients on a WAN.

2.) Your application is mission critical, and any failure would cause severe
distress to the business or

3.) You have a large number of simultaneous users (like, say, more than 50).

Your statement, without these caveats, is just not correct.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

['69 Camaro]

Hi, John.

In addition to the other excellent advice you've received, you may want to
check the VBA code as well. Ensure that you don't have another database
open in the default workspace. Ensure that the VBA code doesn't alter the
CommandBars during the session, either.

Does this database have any Access database library files or any Add-ins
running when you attempt to compact the database? If so, ensure that no
database objects are open in the Add-ins, and that no library database is
using the current database before you attempt to compact the database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tony Toews]

Here is my opinion on the whole thing. No application that is a multi
user application should be using Access as a database. The application
should be using MS SQL Server as the database, which is designed for
multi user usage. Access is not designed for multi user usage, although
people try to do it.

Ah, I guess we won't tell my clients that. One of whom has 25 users
in all day long. Some in A97 on NT 4.0. Others in A2000 on Terminal
Server/Citrix. Yup, it doesn't work for them at all.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[SmartbizAustralia]

Why do some people make silly comments about sql in an access group?

Anyway what version of MsAccess are you using?
What service pack have you applied?

Have you tried to rename the file first to ensure no-one else is linked
to it etc?

There is alot of free code out there as well to check who is logged in,
but with the compact command it seems best to open up MsAccess and then
goto the tools menu and run the compact and then point to the database.
Sometimes I get the locking issue you have otherwise.

Tom Bizannes
Microsoft Certified Professional
(SQL Server 2000 Admininistration and Design)

 

[Arvin Meyer [MVP]]

Here is my opinion on the whole thing. No application that is a multi user
application should be using Access as a database. The application should
be using MS SQL Server as the database, which is designed for multi user
usage. Access is not designed for multi user usage, although people try to
do it.

Well Duane, there's no nice way to put this. You're wrong! A well designed
Access/JET database will do almost anything, at at least 1/3 the cost of
SQL-Server, that a company needs to do as long as the scale is within
Access's specifications.

I've been writing databases since 1981, Access databases since 1992, and as
a professional developer for more than 10 years, and have yet to find a
system as versatile as Access.

The applications I currently support have 53 users on 15 front-end databases
and 1 asp front-end all against a single 100 MB JET back-end. It is quick,
despite users having as many as 3 front-ends open, and probably as many as
65 concurrent connections. At any given time, there can be 8 asp users, 5
users on Terminal Server, and 40 users on the LAN. We haven't had a
corruption since April of 2003 when a bad WiFi card repeadedly dropped
connections. Not only that, a truncated copy of the database has been
running on a web server getting an average of 5,000 hits a day (maybe 3,500
pageviews) with only a single problem since 2001. The same web server runs a
SQL-Server database which was down for a day when the Sasser worm hit. In
all the years I've been running Access, I've lost under 2 dozen records from
corruption, and most of that corruption was due to user error (turning off a
machine during a write).

SQL-Server is a fine database engine too. I use it when I need better
security (hospital records, etc.), when I have a lot of users, (75 or more),
when I have large databases (over 500 MB), or when a client with money to
burn insists on it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer [MVP]]

I definitely agree with you on that! Unfortunately the company I work
for despite being extremely large and having lots of money, doesn't
seem to be too bothered about making something more usable. I don't
think it is very high on their list of priorities. As long as what we
have gets the job done, no matter how long it takes they're not
interested in improving it.

1. Usability is a function of a developer's skill, not a database engine.

2. I find Access front-ends 3 times faster to create than equally complex VB
6. front-ends, and at least 5 times faster than VB.NET or ASP front-ends.
The database structure (tables) are roughly the same effort, simple views
and stored procs are slightly faster in Access, complex queries are
significantly faster to write and debug due to the superior graphical
interface. And reporting recordsources are much faster with many which use
custom VBA functions within the SQL statement impossible in anything but
Access.

I work for Fortune 500 companies too, as well as government clients. The
smart ones use the right tool, at the right price, for the job. The dumb
ones make their decisions on the golf course, or by reading computer
magazines.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access