Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Apr 2008
    Posts
    7

    Question Unanswered: The database has been placed in a state by user “Admin” on machine that prevents

    I don't have a lot of Access 2003 experience. Thanks for any help you can give me here.

    I have a client that has an Access 2003 database that was probably originally created in Access 97 and then I updated it to Access 2000 and then Access 2003 format. They intermittently get the error: "The database has been placed in a state by user “Admin” on machine that prevents it from being opened or locked". This is a 6 user network that has 2 copies of Access 2007 and 4 copies of Access 2003 accessing the database.

    I found this article from Microsoft: http://support.microsoft.com/kb/274211 which seems to indicate that this is just how Access is built by design. I have advised the client to open the database and leave it open all day and then close it at the end of the day to prevent the locking up of the database with the constant opening and closing of the database. Any other suggestions?

    (I have found several resolutions for this error, but they have been so wide ranging, I'm not sure they apply in my environment.) This access database seems to be fairly simple, a few tables and a list of the company's leads.

    Thanks in advance for any help or insight.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I wouldn't advise a client to open an mdb and keep it open all day to avoid a locking problem (in a multi-user environment on the same mdb file). I think you are misinterpreting the article as it is refering to "repeatedly" and "rapidly" opening an mdb several times (within a very short timeframe like every minute or so), not opening an mdb, entering some data (for a while), then closing it for a while (1/2 hour or so), then opening it again to do some more work, etc...etc... You actually take the same risk locking (or even corrupting) an mdb by having several clients keeping the same mdb open all day (ie. via a network hiccup to one of the client's machine, or from their computer locking up, or Access conflicting with another program run on the user's computer, or anti-virus being run, or timeout issues.) This would leave the mdb in a locked state where should 1 user close out, they wouldn't be able to get back into the mdb unless everyone closed out and the mdb was compacted/repaired (which may also possibly need to be done the next day when someone first wants to get into it and no one else is in it - since the mdb was last left in a locked/need to be repaired state from the day before.) You should advise your clients to open it and keep it open until they will not be using it (i.e. entering/retrieving data) for more than 1/2 hour or so and then close out of it (and especially not leave the data form bound to a table open for a long period of time when they are not using it but instead close to a non-data bound form such as a menu form when they don't need the bound data form open - see unbound forms note below). They just shouldn't be opening the entire mdb every other minute to enter or lookup data. With 6 users in the same mdb (which a lot of people say is pushing it for a single client-server jet table mdb), if they do need to constantly access the data throughout the entire day though, I'd personally recommend using the solution in the following link which will solve the problem (and not cause other issues)...

    http://www.dbforums.com/showpost.php...6&postcount=19 but only if the tables are linked in from a split backend/frontend (which I would advise doing in a client-server atmosphere with multiple users.) The solution in the link above has always worked very well for me and I've never ever had a locked mdb issue (and it will work regardless of the version used to open the frontend mdb and regardless of the number of users using the mdb! (I've had 50+ users.)) Since using this solution entails each user having their own interface which is cloned from the source mdb (each of the frontends will then have the same tables linked to the same backend), the user can keep their cloned mdb open all day. But I'd only advise that they close the data form and return to a non-data bound "Main" menu as leaving the data form open constantly all day can cause timeout/record locking issues (Note: which is where you'd start getting into designing unbound forms to avoid this issue (which I'd also highly recommend!)) Using the solution in the link above will solve the issue of a user unable to get into the mdb because the mdb is opened/locked by another user (regardless of how the other user locked or how the mdb somehow got into a locked state.)

    Either way, they shouldn't again, be opening and closing the mdb repeatedly every minute even with this solution.

    Also consider making an mde and having your users use a frontend mde versus an mdb (along with the cloning solution which works the same for a frontend mde.) Keep the backend though as an mdb.
    Last edited by pkstormy; 04-23-08 at 04:13.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I get the same error every once in a while when I have modified a module and not saved it before running some other code. Is anyone going into design view for anything? Change a query, edit a form, etc. In 97 two or more people could be making programming changes within a database at the same time (which lead to alot of weird corruption problems for us).

    The reason I bring this up is someone could have programmed a report to open in desing view, moved a control or two then open the report in preview mode. This would have worked in 97 but in 2000 and later you can't do it in a multi user environment.

    I would definitely be interested in any results you find. My wife is having the same problem with and ADP that she uses at her work. I haven't had a chance to troubleshoot it for her though.

  4. #4
    Join Date
    Nov 2007
    Posts
    38
    I had this and Healdeam (I think it was) advised compact and repair..this seemed to clear up my problems

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    A compact and repair will most likely fix a locked mdb problem (providing all users have exited the mdb) but won't prevent it from happening again given the situation fantasmo originally presented.

    Having many users in an mdb throughout the entire day (with jet tables and bound forms) risks the problem of a locked mdb happening again. The advice I gave in my post is a way to permanently prevent the problem from ever happening again.
    Last edited by pkstormy; 04-24-08 at 16:58.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Apr 2008
    Posts
    7

    Update

    I have an update on the situation. We have an Office 2007 PC that NEVER has any issues accessing the database from our Small Business Server 2003. This PC typically is the first to open the Access database every day. The other machines using Office 2003 and Office 2007 intermittently have issues accessing the database with a message that the admin on the first Office 2007 PC has it locked. I've been trying to recreate the specific error message to post. I have a feeling there are additional security features in Access 2007 that are locking out the other issues.

    Thank you all for responding to my post. Your help is appreciated.

    Todd

  7. #7
    Join Date
    Apr 2008
    Posts
    7

    Separate issue?

    Additionally on a workstation running Access 2003, I am getting an error when I go into one of the tables. I get a "the command or action refresh isn't available now. You may be in a read only database or an unconverted database from an earlier version of Microsoft Office Access. The type of object the action applies to isn't currently selected or in the active view. Use only those commands or macro actions that are currently available for the database.

    Next, I get a screen that indicates that the action failed

    Macro name: Refresh
    Condition: True
    Action Name: RunCommand
    Argument: 18

    When I cancel out of this error, i get an error: There isn't enough memory to update the display. Close unneeded programs and try again.

    I'm not sure what all this means, maybe that my macro for this table isn't legit?

    Thanks again for any help.

    Todd

  8. #8
    Join Date
    Apr 2008
    Posts
    7

    One more thing....

    I have run a compact and repair against the database with no change.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    "The database has been placed in a state by user “Admin” on machine that prevents"

    ..to me that indicates that someone has opened up soemthing in the application and made modifications to it, or has started to make modifications.

    the 'best' way of stopping that is to deploy the app as an MDE, OR if you MUST allow users to modify the application then give them an individual copy of the front end.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I wonder what other programs are also running on the pc, if the out of memory problem doesn't also happen on other pc's, and lastly, if there isn't possibly a recordset which isn't closing somewhere in code.

    Have you tried monitoring the Access mdb file in Task Manager and watch it's mem growth as it's being used? If the mem growth keeps growing and growing, it could lead to a problem such as multiple recordsets being opened and not being closed properly. Not closing a recordset is a big no-no which can lead to out of memory type problems.

    It may not be the solution to your problem but it doesn't hurt to open Task Manager and watch the mem usage of the mdb file as you do things which involve opening the recordsets. I had one which kept growing and growing until it finally crashed (out of memory) and the reason was due to several recordsets which I opened in code and never closed (my first (and last) lesson on always closing recordsets I opened). When you watch the mem usage in Task Manager, you'll initially see it grow as you hit the recordset code, but you should see a stabalization point where it stops growing any more. A 20meg mem usage which grows to 100meg or more and still growing in Task Manager is usually a good sign of a leak somewhere. Usually 30-50 meg is where it caps off (depending on the application, ie. how many forms and recordsets you're opening.)
    Last edited by pkstormy; 05-02-08 at 01:29.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Apr 2008
    Posts
    7

    healdem,

    how do I deploy the database as an mde? I don't have a lot of access troubleshooting experience. The mdb is currently on a network drive on the server where users connect to it.

    thanks.

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I personally like to use winzip and make an exe file out of it. The user runs the winzip exe file and it extracts/copies the mde to the appropriate location.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ... or are you just after Tools - Database Utilities - Make MDE File ?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Good point StarTrekker. I think you're right and he meant how to create an mde out of an mdb.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ... maybe We'll see when he returns I guess
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •