Share via

The database has been placed in a state by user

Anonymous
2011-08-25T15:42:28+00:00

Hi,

I have a accdb file in shared mode without locks containing everything (tables, forms, reports and code)

I get the following error: “The database has been placed in a state by user ‘Admin’ on machine… that prevents it from being opened or locked”

Before moving from XP to Win7, I used to open the database with shift key (to prevent any code execution at startup) on one machine and leave it open. This used to be enough to avoid the problem. This not the case anymore with Win7.

I’ve red several articles and answers on forums. One of them (http://www.source-code.biz/snippets/vbasic/10.htm) advices to open the same db a second time with vba. It didn’t help fixing the error.

But most of articles advices to split the database and put a copy of the FE file on each machine. Before applying this advised solution, I would like to know what is the meaning of setting the default opening mode to ‘Shared’. If I cannot share an mdb file with this stetting, what’s the purpose of putting such an option then?

2 question:

Splitting a database is recommended first to “reduce network traffic” because not all the objects are loaded. I can afford such traffic with our network of 1Gbs and I don’t see how it could be faster. Response is instantly. As a second advantage: simplify the “front-end development”. I find it much easier to update one mdb file when nobody is connected to it than go through each machine and make sure they have the latest version (but this is my point of view from my perspective).

My second question is: if these are the advantages of splitting a database how can this solution prevent the error of the database put in a state?

I thank you in advance for your time and help.

Regards

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

13 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-08-25T20:10:20+00:00

    A few questions I have are:

    +>Are you able to open the database with one user or does it not open at all anymore?

    +>If you can open it with one user, then does this occur when the second user opens the file?

    +>Where is the database file located?

    +>Does each user have full control over the folder the database is located in?

    +>When you open the database or Access, do you see any add-ins that are being loaded.  For example an Adobe or Avery add-in?

    Best Regards,

    Nathan O.

    Microsoft Online Community Support

    0 comments
  2. ScottGem 68,785 Reputation points Volunteer Moderator
    2011-08-25T20:28:05+00:00

    Hi,

     

     

    I get the following error: “The database has been placed in a state by user ‘Admin’ on machine… that prevents it from being opened or locked”

    This error indicates that someone has opened some object in the database in Design mode. That would prevent others from opening it.

    By splitting the database, especially by using a local front end, this problem is completely eliminated. Even if a user opens their FE and opens design mode on an object, they are locking only their front end. So no other user is affected. Since the tables are linked they can't be opened in Design mode so no locking would be involved.

    This option also means you do not have to wait for for no one to be using the database to do design work. Your design work is done on a development copy and moved to production when its ready. As to deploying the updated front end their are tools to make that easy. The most popular is AutoFE Updater but I believe Bill Mosca has a similar solution. So its not necessary to go to each machine to update them. Its done automatically.

    16 people found this answer helpful.
    0 comments
  3. Anonymous
    2011-08-25T20:45:51+00:00

    Thank you for your interest,

    - Yes I am able to open the database with one user. But the error occurs sometimes when the 2nd database get connected to main one. The connection is done as link reference in VBA (Tool menu, Reference -> main db).

    - Most of the times yes, this occurs when a second user connects to the main db.

    - The database is located on Win 2008 server.

    - Yes all users have read/write permissions on the shared directory.

    - From what I can see in the database Addins options, there is only “Adobe PDFMaker Office COM addin” that is active.

    One precision though concerning the second db that is connected to the main, since it is the one generating the error. As I said, the second database references the main one as a library because I use the same procedures and don’t want to duplicate them. Also, the second database contains linked tables on the main database that are used to inserts or update data from both main and second databases.

    If I follow Scottgem when he says “This error indicates that someone has opened some object in the database in Design mode”, I cannot determine where in infrastructure mentioned above is trying to open the database in design mode.

    0 comments
  4. Anonymous
    2011-08-25T20:55:32+00:00

    Hi Scottgem, Thank you for your message.

    I’m not sure that splitting the database will make my development and deployment easier. Without splitting, today I have a database copy for development where I am free to change and test my code at anytime. When I am ready, I simply ask my (5) users to disconnect, generally during lunch time or evenings and then I replace the production by the development copy.

    I have already tested a spitted database. It was slower by one second from the non-spitted! And I thought  already on how to update the FE files. On the startup, it can check for local version compared to the server version. If the end user doesn’t have the latest, then I run a batch file and close the database. The batchfile will download the copy from the server and past it on the en machine. When done open the new file.

    Now I simply want to understand where my code is trying to open the database in desing mode as you said if this is the source of my problem

    2 people found this answer helpful.
    0 comments
  5. Anonymous
    2011-08-25T21:05:43+00:00

    In regards to the error message you are seeing, if you aren’t using the Adobe PDFMaker Office COM add-in, then I would recommend disabling it on each machine and then seeing if you still have the problem.  I don’t know why this produces the error, but I assume it is actually related to what Scott was describing.  In order for this add-in to load itself into the toolbar it almost seems like it needs exclusive access to the database in order to make this design change.  If you can disable this add-in through the add-ins option on each machine, I would try that first.  If that doesn’t work, I can provide you with a registry key to modify in order to ensure it is disabled.

    As far as splitting your database goes, as Scott mentioned and as you have heard from other people, this is the recommended approach.  Another large benefit of splitting the database is it can help reduce some of the risk with database corruption, due to the decrease in network activity. 

    Best Regards,

    Nathan O.

    Microsoft Online Community Support

    0 comments