Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Unable to compact/repair database

Discussion in 'Information Technology' started by John, Jul 28, 2006.

  1. John

    John
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    World Italy Chief Unable to Appear before Akaal Takht Breaking News Jul 23, 2011
    Controversial Unable to provide relief to ‘aam admi’ Senior bureaucrat seeks premature retirement Hard Talk Apr 14, 2010
    Sikh News Cash-starved Punjab unable to take call on octroi (Express India) Breaking News Feb 16, 2008

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    John wrote:
    > 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.

    --
    Joseph Meehan

    Dia duit
     
  4. Duane Arnold

    Duane Arnold
    Expand Collapse
    Guest

    John wrote:
    > 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 :)
     
  5. John

    John
    Expand Collapse
    Guest

    On Sat, 08 Jul 2006 22:33:15 GMT, "Joseph Meehan"
    <sligojoe_Spamno@hotmail.com> wrote:

    >John wrote:
    >> 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.


    Thanks. Yes it is a shared database.

    John
     
  6. John

    John
    Expand Collapse
    Guest

    On Sun, 09 Jul 2006 07:29:39 GMT, Duane Arnold <"Do forget about
    it"@PleaeDo.BET> wrote:

    >John wrote:
    >> 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.


    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
     
  7. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

    "Duane Arnold" <"Do forget about it" wrote:

    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 :)
     
  8. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

    "John" wrote:

    > Thanks. Yes it is a shared database.
    >
    > John
     
  9. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

    "Tom Wickerath" wrote:

    > 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
    > __________________________________________
    >
    > "Duane Arnold" <"Do forget about it" wrote:
    >
    > 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 :)
    >
     
  10. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    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.


    "John" <spongebob@squarepants.com> wrote in message
    news:q5b0b2php14sm5s2er4ebhc4th29i89iuo@4ax.com...
    > 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
    >
    >
     
  11. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    Duane Arnold <"Do forget about it"@PleaeDo.BET> wrote:

    >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
     
  12. SmartbizAustralia

    SmartbizAustralia
    Expand Collapse
    Guest

    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)
     
  13. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    "Duane Arnold" <"Do forget about it"@PleaeDo.BET> wrote in message
    news:Df2sg.5333$ye3.468@newsread1.news.pas.earthlink.net...

    > 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
     
  14. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    "John" <cow@chicken.com> wrote in message
    news:50c2b2lg1bd010f1jhp3olm6sh2gpcvcqh@4ax.com...

    > 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
     

Share This Page