MDB file permissions for multiple users

[sseto23]

Hi Everyone,

I haven't been back here for a while now, but you guys were so helpful to me before I thought I'd try asking for help on another question I have.

I have an Access database that is split, and each of the 6 users have a copy of the frontend database on their hard drive. The backend database is stored on a shared drive to which they all have access to.

The question I have is what are the file permissions required on the mdb files (I guess especially the backend database) so that the users can access the data at the same time, and modify or add data. So far everyone has "Read", "Write", "Read & Execute" and "Modify". Do they also need "Full Control"? I guess my concern is that if I granted them too much access they might end up locking each other out.

Thanks in advance for any help you could provide.

 

[sseto23]

Here a bit more information:

When myself and another user try accessing the same database, via our own copies on our individual PCs we can open the frontend but once she accesses the backend database tables (ie. loads some data to one of the application forms), I am locked out of the backend database completey. I get a message saying that I 'cannot access the backend database because it is in use by another user'.

It appears that the backend database is opening up with an exclusive lock, but when I check the properties it's set to open as shared and with record level locking.

It's funny because this used to work when we it was on our Novell server, but doesn't after they migrated the access database to a Microsoft server.

Any ideas????

 

[gemma-the-husky]

they need full control of the folder - not least because using access requires them to manage a temporary file called a .ldb file

you do not need to worry about multiple users, and record locking. access handles all of this automatically. if you want to reseach, look at "optimistic locking" which isa the default. There are more advanced "pessimistic" strategies, but these are harder to implement, and generally not necessary.

The only issue with access is security - as your users can see the folder they can also copy and acquire your data. It is hard to prevent this in access. you can use a SQL server backend, but this is more complex to set up and manage.

you should also probably give your users an mde file, rather than an mdb, to protect your application.

 

[sseto23]

Hi gemma,

Thanks for replying.

I have my Access interface options set up with:

Default open mode = Shared
Default record locking = No locks
Open databases using record-level locking is checked off

I tried setting the permissions on both the FE and BE so that everyone has full control, but I still encounter the same problem...

It's funny because it almost looks like the first user who tries to access the BE gets an exclusive lock and ends up locking out everyone else totally regardless of whether the first user is just retrieving data to browse or actually editing the data.

Do you know if the record-level locking information file has anything to do with it (just a shot in the dark because I'm so frustrated!!!) When it gets created and I check the security permissions on the ldb file it shows that only SYSTEM and the id of the first user has any type of access.

This all resulted because they wanted to migrate the Access db off of the Novell server to the Microsoft server!!! I'd say go back to Novell!!!! sigh.

 

[sseto23]

Oops. I meant thanks Dave. I didn't see your profile information until I had hit post reply and noticed that Gemma was your dog.

 

[gemma-the-husky]

there is an open mode called "exclusive access". maybe the first user is inadvertently opening in that mode. Try opening access, and THEN opening the dbs. I am not sure if, or where there is an options setting for this.