Multi User Access Problems!

[reimondo]

Hi,

I have a problem with multiple users accessing the same MS Access 2000 database at the same time. The database is stored a directory under windows XP Professional which is set as a ‘shared folder’ enabling other users to edit the files. The database is stored on the server machine within a networked workgroup of 4 computers. The database file is not split up, and I have set the database file properties in access to be in ‘shared’ mode and not in exclusive mode. I have not set/changed any of the security user/group permissions under Access.

The problem is that if the database is open on one computer, and someone else tries to access the database at the same time, the database won’t open at all. An error message appears stating:
“The DB has been placed in a state by user ‘Admin’ on machine ‘Louise’ that prevents it from being opened or locked.”
When I try to read up on the help page, it doesn’t give me any additional information other than it’s an error #3734. I have followed all the instructions carefully in Microsoft Access and Windows XP on how to share a folder/DB file, but I still get this error message. The only way the DB file can be opened over the network, is if it is not already opened. However, I have Word document files in the shared directory which can be opened by multiple users at the same time.

I am designing and installing this database for a friend at his workplace. The strange thing is that the multi user access works perfectly when I install the database on the lab computers at university. The lab computers are also using Windows XP Pro, the only difference being that the file is shared using permissions via the Properties > Security tab. Due to the way the lab computers are set up, I can only share the folder via the security permissions, and not via the ‘shared folder’ way as done at the friend’s workplace.

I am really puzzled here as to why multiple users cannot open the same DB file on the workplace’s computer? Does this seem more of an Access problem or a Windows XP network-sharing problem? Any help will be most appreciated.

Thanks,

Raymond

 

[Autoeng]

The "shared" settings for opening a db are exclusive to each users machine. Make sure that Access on each users machine is set to shared. Split your db, you'll thank us later.

 

[The_Doc_Man]

I have not set/changed any of the security user/group permissions under Access.

If this means you haven't yet implemented workgroup security, you have already set yourself a more difficult task in the long run.

One of the things about forcing folks to join a workgroup is that you can grant them only SHARED access rights through that workgroup account entry.

There are many threads here on Workgroup Security, including several where I have advised folks on how to do the first part of a workgroup lockdown that prevents casual browsers from locking up your shared DB. Search the forum for my name and the subject of workgroup security, you should find my advice. When you see a post talking about what to do to the Admin and Users groups, that's it.

 

[Waltang]

Try going to Tools -> Options

Then goto the Advanced tab

Make sure that the Default Open Mode is set to Shared, and not exclusive.

I can't remember for sure, but you may need to set this at each machine.

 

[Pat Hartman]

Make sure that your users have sufficient network security to create and delete files in the directory tha holds the Access db. For a db to be shared, Access needs to create (hence the necessity for create permission) an .ldb file. This is where users and activity are tracked. When the last user closes the db, Access deletes the .ldb file (hence the need for delete permission). The delete permission isn't critical but it is best to not leave the .ldb hanging around.

 

[reimondo]

Hi The_Doc_Man,

thanks for the reply.
I'm still reading up on the workgroup security stuff, and i have a question to ask. I'm wondering why the error:
“The DB has been placed in a state by user ‘Admin’ on machine ‘## that prevents it from being opened or locked.”
only appears when I run the database file from the office network?

If I run the same database file with multiple users on the XP computers at my university, it works fine.
So I'm thinking, if the problem lies within not properly defining a 'security workgroup', why doesn't the error occur at uni aswell?
As I would think, the same error would occur no matter where you use the database, since it's still the same database?


Thanks,

Raymond

 

[Pat Hartman]

Not so. Did you read my suggestion?

 

[reimondo]

At the moment, I'm trying to emulate the error message
"The DB has been placed in a state by user ‘Admin’ on machine ‘## that prevents it from being opened or locked.”

on my computer at uni, but I can't seem to get the same error message. As this error message only occurs when I try to access the database at the workplace. Right now I'm trying to emulate this same error at uni, so that I can understand why it occurs and how to solve it. Does anyone know exactly why it occurs and how i can recreate this error?

I've tried modifying the security workgroup settings, but I still can't get the same error message. The closest error messages I can get are only those that deal with 'exclusive access' issues, which are different to the original error message - 'DB prevented from being "opened or locked"'. So I'm thinking it is not directly related to the 'shared' or 'exclusive' access issues under the security workgroup settings.

I've also tried changing the network permission settings on the database folder, as suggested by Pat. If I set the permission of a directory such that the other other users cannot 'modify' or 'write' to the directory, then Access only returns a error message "the lock file cannot be created", which is still not the same as my original error message.

So I'm really puzzled now, can anyone offer any suggestions?
As I wish to re-create the same error message on the uni computers, so that I know what causes the error exactly, fix it, and and then apply it to the version at the workplace.

 

[reimondo]

Another thing, this problem shouldn't be related to whether the database has been split up or not right? As the version I have at the workplace has yet to be split up. (but because I don't visit the workplace much due to my work being contract work, I haven't had the chance to test it out on the office computers...as usually I try to get everything working first, then install it at the office)

 

[Pat Hartman]

Try this article:

ACC2000: "The Database Has Been Placed in a State by User..." Error When You Open a Database Containing a Form That Has a Subform

 

[Nemi]

At the moment, I'm trying to emulate the error message
"The DB has been placed in a state by user ‘Admin’ on machine ‘## that prevents it from being opened or locked.”

on my computer at uni, but I can't seem to get the same error message. As this error message only occurs when I try to access the database at the workplace. Right now I'm trying to emulate this same error at uni, so that I can understand why it occurs and how to solve it. Does anyone know exactly why it occurs and how i can recreate this error?...

Yes. It is quite simple to generate that particular error. I have found that if you open the DB you want to see the error appear in. Then open another DB and try to export a table into the first open DB. It will produce the error in question. It appears to be very similar in nature to windows error when an object is already in use.

 

[JACKSON]

I didn't have time to read everyones respones, but aside from checking the shared and security permissions on both the folder and the access file, I would make sure that everyone on that network is signing in under a unique user name in regard to both access system.mdw file as well as their windows sign-in name. I have run into a similar problem, and it generally is when everyone signs in using the default "Admin" user name, and security has not been set up on the database.

Hope that helps.

 

[Daffersd]

A related question

Apologies for butting into this thread with my own question?

I think I need to go through the security side of things on my database too, but this one got me tonight.

I did a Compact and Repair on my main database, then I ran a macro in an excel sheet on my portable (p2p on shared folder) that looks up values in the database, it failed to work saying it was locked, now the database was set to shared, I had made no design changes it threw me.

So I came out of the database on the main PC and tried to access the database. Access told me that that it can not find the file, even though its sitting there smiling at me in Windows Explorer?

The database is saved as a Name.db1 and the portable is trying to locate Name.db1.mdb. The database is Access 2000, while my portable runs Access XP.

Wierd!!!