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

 

[hymn]

have a complete look at thread181-504923

Hope this helps
Hymn

 

[psemianonymous]

1. (unlikely) If you are using "db.Close" in procedures, you may be sending the database into passive shutdown mode. The error message you described isn't necessarily a bug--it's a feature. Believe it or not.

2. (likely) If you are editing forms or reports while they are using the database, this will continue to happen. Don't be editing database objects on the production database.

 

[Reimondo]

I've tried following the two suggestions from hymn and foolio, but it still doesn't help.

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 so i can test it out, 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. But 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 then apply it to the version at the workplace.

 

[hymn]

Try going to each computer at work and look in the tools/options and see what the record locking is set at on each one

Hope this helps
Hymn

 

[FRrogoy]

I know this is an old thread, but I was searching to see if data sharing was still a problem in Access. I have had problems in the past (Access97) and I don't know that MS has improved Access in the security area. Below is what I experienced, and my solution and other suggestions. I know the problems weren't imagined - and we wasted a lot of time pointing fingers and analyzing what was happening.
We had several users (6) around the world who had to share (update) a common database. The problem we had was overlaying each others' data. We tried the various locking and security settings. What we eventually determined was that no matter what the security settings are, Access reads and writes a buffer (2k, for example) of records. So even though you might have had only one record locked, you wrote back several (depending on record size), potentially overlaying another record that someone else had just updated.
Our solution at the time was to create a master database and distributed copies that communicated with the master, via csv files. A more elegant solution is to use a more robust back end (RDBM) on the server, like SqlServer or Oracle.
I'm sure some of you will pooh-pooh this, and may even think this problem doesn't exist. But maybe others now have another place to look if they have randomly disappearing updates!


Frank

 

[BNPMike]

"What we eventually determined was that no matter what the security settings are, Access reads and writes a buffer (2k, for example) of records. So even though you might have had only one record locked, you wrote back several (depending on record size), potentially overlaying another record that someone else had just updated"

That cannot be true. I don't have a very high opinion of Microsoft's children but even they wouldn't write a locking scheme that doesn't.

Were you doing something unusual, like using snapshots for example?

 

[nimarii]

Reimondo,

Were you able to fix the problem? I'm having the same issue, and its driving me nuts, I can't seem to find a solution anywhere.

nimarii

 

[TheTomCat]

Reimondo,
If you DO NOT have the database split up, try this.

On EACH user, go to TOOLS\OPTIONS.
Then set the Default Open Mode to EXCLUSIVE and the Default Record Locking to ALL RECORDS.

I know it may not make much sense, but this is the only way I have gotten a Non-Splitup database to work in a Multiuser environment.

Good Luck,

TomCat

http://www.reportcop.com

 

[easyit]

Hi,

I would suggest that your main problem is that every user is Admin with admin rights. This is because you have no proper security implemented. This means that everybody can alter objects in the DB (change forms, alter recvords in tables etc). I say can, not that they are. But because they can, it is very well possible that one of them, by accident or not, is switching access to exclusive mode (basically that is what is happening when you try to change a object). After that, other users can get the named message. you can try to emulate this behaviour with a tets database, open it exclusively and try to open the DB from another PC.

The easiest way to solve this is probally to split the DB and have every user have a copy of the front end part on their local HD. You may still have problems in this way, but it is less likely.
Better is to split and secure the DB, assign users proper rights...

Maarten

 

[psemianonymous]

I wholly agree with easyit. If it's a problem, split the database and give everyone their own frontend. You can even leave the separate frontends on the server if you want (it's crazy, but it helps if you don't want to do the auto-FE updating).

I usually at this point paste in the USENET thread where someone mentions that Access200+ use a single "blob" to store all objects in the database, as opposed to one "blob" per db object. This means that if someone edits "frmNoOneUses", it still affects EVERYONE IN THE DB. You can google for the thread yourself; the result is the same. Split the DB.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[nimarii]

thanks easyit & pseale.

I split my db with the FE on each user's HD, and it seems to be working just fine (knock on wood). I still haven't set any securities in place, namely bc I'm not familiar with how to do it. Is having securities in place that important? We only have 4 users.

 

[psemianonymous]

If you trust your users, security won't be a problem--I don't recommend security for everything, just security where you have sensitive data or lots of data or lots of data entry per day. In other words, you'll be fine.

 

[easyit]

Please keep in mind that without security every user has admin rights. They are allowed to make changes to tabledefinitions, forms etc.