can't open - locked by admin

[tizwaz]

since converting a database from access 97 to 2000 if anyone opens anything in design mode even if they then close the form/report etc other users cannot open the database and are getting the database has been put into a locked state by admin on ....
This is a problem as I have a number of new reports to develop and some changes to existing forms.
If I split the database and work on a copy of the front end but still linked to the current back end will that still lock people out?

 

[MoLaker]

It sounds like you are opening the database 'exclusive' and this is locking not only the front-end you're working on, but the back-end it is linked to, as well. If you are working on a non-production copy of the database, try opening it shared instead of exclusive.

As for the other users, make sure the database is set to open 'shared' and not 'exclusive'. Look under Tools|Options - Advanced Tab - Default open mode.

 

[tizwaz]

I haven't split the database yet but wondered if I need to to solve this problem. The database is set to shared in Tools/options already. I wouldn't mind if it locked others out while I amended something but once I've had anything in design view it locks others out even though I no longer have anything in design view.
Its not just me it does this to - I opened a table in design view on someone elses pc and then closed it and when someone else tried to open the database they got the message about it being locked by admin and it was this persons pc number it quoted.

 

[MoLaker]

Sorry 'bout misunderstanding issue of split database.

Take a look at the directory where the database resides when no one has it open and the problem exists. Is there a file with the same name as the database with an extension of .LDB? If so, it is probably because at least one user does not have Delete permissions for the directory. Access creates this file when anyone opens the database then deletes it when everyone closes. If the last user does not have delete permissions, the file will not be deleted. If the database record locking is set to 'All Records' and a user makes changes then quits, then the locking file (LDB) might keep the table locked if it is not deleted.

Two things to check.
1. Make sure all users have Read, Write and Delete permissions for the directory where the database resides.
2. Set database Record Locking to 'Edited Record' (unless you have some underlying reason not to). Go to Tools|Options - Advanced Tab - Default Record Locking.

 

[tizwaz]

I know that everyone has read,write, modify, erase and file scan to the directory. The record locking is, I think, set to none. Can't check until back in work on Monday. The lock clears if the person in question closes the database but doesn't clear when they come out of design view. Does this mean that when anyone has been in design view they need to close the database to clear the locks?

 

[MoLaker]

I'm going to fall back and regroup. You've been saying all along that the problem occurs after a user goes into design view, but I somehow was zeroing in on changing data. I found this article on MSKB.

http://support.microsoft.com/default.aspx?scid=kb;en-us;200290&Product=acc

It would seem this "problem" is a feature AND is new with Access 2002. Although, I do not interpret the article as saying the user must exit the database to unlock it. It does say,

Access maintains the exclusive lock until the user saves or discards all dirty objects and no other objects are open in Design view

- which might be a clue.


I might ask, why are other users opening anything in the design mode? Are they also designers of the same database?

 

[tizwaz]

Hi
thanks for your continued help. It is Access 2000 that we are using not 2002. I have checked and the defaults in options are shared, and no locks.
Normally other users would not be in design mode I just happened to be looking at something in design view on someone elses computer but I had then come out of design view before the other user tried to open the database.
Looking at your quote from access 2002 coming out of design view should clear the locks so I don't know why it hasn't.

 

[MoLaker]

My typo - as the link states, the problem IS Access 2000.

So, are you're talking about a one-time occurance here or can you repeat this problem? How about on another machine? If it is machine specific, you have a whole different ballgame. Watch who you are logged on to the network as, too. Try logging on to the network at your machine, for example, using the user name and password of the individual where you had the problem (if you can).

In general, I don't think we know enough about the conditions causing the problem - just yet.

 

[tizwaz]

so far its happened to 2 of us on 2 different machines. (myself who had superviser status and an ordinary user) Will have to do more investigations to try and track down what is causing the problem.

 

[jbento]

I have found this wonderful little database that logs users off automatically, so designers can do their designing. Go here:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=LogUsersOff.mdb

It explains all about it, and you can download the sample as well.

Thanks,


Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[nightjam]

I am using ADO to open an access 2000 table and do updates but keep getting "database in a state by user which prevents from being open or lock".

How can I unlock this state using VB coding? No one is using the db as im the only user, and the form does not have a control source so table should be free.

Here a sample code of what i am doing


Dim db As ADODB.Recordset 'Database object for table
Set db = New ADODB.Recordset

strFile = "provider=microsoft.jet.oledb.4.0;data source=" & CurrentDb.Name
textt = "Select Orders.* From [Orders]"
db.Open textt, strFile, adOpenDynamic, adLockOptimistic
'Response with error after this line
"Database has been placed in a state by user XXX on machine XXX which prevents it from being open or lock

 

[MoLaker]

Check for a locking file (same name as DB but with extension of .LDB) while no one is in the database. If one exists, delete it. Then make sure any user running the DB has at least Read, Write and Delete permissions on the directory. Last, make sure you properly close and destroy all ADO recordsets and connections.

 

[nightjam]

****RESOLVED****

I had a combo box which row source queried as a criteria a textbox value on my Form. This somehow prevented the use of ADO access to the table.

The corrective action i did was delete the row source at ADO access and after ado closure changed the row source back.