Multi-user accessability issues, workgroup needed?

[andysgirl8800]

Hate to throw this issue back in the mix, but I seem to be really struggling to find a solution, even after extensive searching through posts. I can't seem to pin down any existing posts that directly addresses my problem, so here it is:

I have a DB that needs to be accessed by no fewer than 9 users for data entry and retrieval, no designing or programming. I seem to be running into a multiple user problem revolving around exclusive opening of some sort.

As it stands, about 4 users can open the DB simultaneously without issues. But as soon as a 5th person tries to open, they get an error saying they do not have exclusive rights, and the other 4 members run into problems closing their opened forms or tables with a "cannot save due to another user already has it open" and an error loop. Then, no one can close because of the auto save function in access.

I believe this may have something to do with workgroups, and have searched the forum with this keyword, but I'm not sure if this is where I need to go. I also don't know how to set it up from start to finish. Do I need to do something on each user's individual computer to make it work? And once I have one set up, will this absolve my current multi-user issues by specifying a shared access at all times?

Please help, as I'm getting a bit tangled up in the steps of this process. Is there a different solution? Thanks for any advice you can offer.

 

[pbaldy]

Just to clarify, a multiuser application should be split, with a copy of the front end (forms, reports etc) on each user's PC and the back end (data tables) on the network. Is that the case here?

 

[andysgirl8800]

No, we don't have any FE/BE structure set up here. Don't know how to do that, not sure if we COULD do that. The DB is on a shared drive at this time.

 

[pbaldy]

That is basically the accepted standard in multiuser applications. You're asking for corruption problems to have multiple users accessing the same copy of a db at the same time. It should stop your problem, since each user would have their own copy.

 

[The_Doc_Man]

When this occurs, the offending user has probably not joined the correct workgroup. Further, you have not correctly set up your security. Search the forum again, this time including "Open Exclusive" as part of your search.

Now, in summary, here is why you might be having the problem.

When Office is installed, you get a "default" workgroup. In this workgroup, there is a user called "Admin" who is a member of the "Admins" group and WHO DOES NOT REQUIRE A PASSWORD.

By default in any .MDB file, the Admin user has OPEN EXCLUSIVE rights on the database object.

The last workgroup you joined is a REGISTRY-level entry. Once you install Office, you ALWAYS have a defined workgroup. Even after a reboot, you are still a member of that workgroup.

You must do some things to control this.

Create a non-default .WKG file that your users can join. (And that you can join.) Best option is to put it in the same folder as the .MDB file, but perhaps restrict it from being modified.

Make another user entry (like yourself, e.g.) a member of the Admins group. (Note the "s" on the end of that.) Make the Admin user (no "s") a member of Users but not of Admins. Be absolutely sure that Users (as a group) DOES NOT HAVE EXCLUSIVE ACCESS to the database object. Also assure that Admin (as a user) DOES NOT HAVE ANY ACCESS RIGHTS AT ALL. Let Admin derive rights from the group just like everyone else.

If you REALLY want to force the issue, take away ALL ACCESS RIGHTS from the Users group except for the READ permission. I don't think you can revoke all rights. But if you can revoke permission for Users on an object, do so.

Another thing that you should do is NEVER EVER IN A GAZILLION YEARS grant any access rights to a user. Instead, grant your rights to groups other than Users. Create your own groups, one group for each possible user role. Then make your users become members of the Users group (required by Access) and your home-grown role-based group. Convey ALL permissions from your groups rather than the Users group.

In other words, do your level best to make users coming in from the default workgroup and default admin user so bloody frustrated that they will do anything to make it work better - like join the correct workgroup.

 

[ghudson]

Another common problem is that one or more users might not have the correct network access rights to the directory where the db is located. All users must have full rights to all files within the directory that the db is located. Each user must be able to open, read, edit and delete the files within the directory where the db is located. Everybody has to be able to create/edit/delete the database record locking file [.ldb].

As mentioned above... You must split your database and install a copy of the front end onto each users computer and all front ends must be linked to the one backend on the server.

 

[Banana]

Wait, wait.

Does that includes the BE? Then how are you going to protect users who may not have full priliveges in Database (e.g. they are data entry only and cannot browse certain records), from opening BE directly?

I could set the password for the BE, but I'd like it to be at network level, not file level... :\

 

[andysgirl8800]

Thank you for your replies. I am still confused though on if I need to establish a custom workgroup, as The_Doc_Man outlines, or split the DB with FE/BE setup. Do I need to do both? What kind of programming is required for the latter option?

 

[ghudson]

Banana,

Then you need to properly secure your database [front end and back end] with a custom workgroup and user permissions. Access security is a tricky beast for the newbies but once you have it down your databases will be secured so that the users can not access any database objects that you do not want them too. Search around for there are lots of posts regarding Access security. Read a bunch of them and try to learn from the posters mistakes before you jump into securing your database. Once quick trick you can try is to password protect just the backend. Once you have split your database... set an on open password on the backend. Then when you link your frontend to the backend tables you will prompted to enter the password each time you link a table. Access will store the password within the link but the user will never see it or be able to find it. They will not need to supply the password when they use your frontend. That keeps them out of the backend but they can still access the tables and data from the linked tables in the frontend unless you secure the frontend. Good luck!

 

[ghudson]

Thank you for your replies. I am still confused though on if I need to establish a custom workgroup, as The_Doc_Man outlines, or split the DB with FE/BE setup. Do I need to do both? What kind of programming is required for the latter option?

You need to do it all. Securing the db does not require programming but you will need to use VBA for the user to only be able to do what you want to allow them do do with the data and the db.

 

[andysgirl8800]

Thank you again for your guidance. Is there something I will need to do on the individual computers for each user?

 

[The_Doc_Man]

For security, no. For other things related to "References" - maybe. But we'll cross that bridge when we come to it.

 

[andysgirl8800]

Alright, so I followed the MS Access Security FAQ, went thru all the steps very carefully, set up the custom workgroup file, established all the users and groups accordingly. Now, if I log in as the Administrator ID...no problem, the DB opens. If I try to log in as any of the users that I set up with their PW on the DB in question, I get the error:
"You don't have the necessary permissions to use the 'G:\QRM\Secure QRM Ddatabases.mdb' object. Have your system administrator or the person who created this object establish the appropriate permissions for you."

I thought I just spent the last 2 hours setting up those permissions!? What did I do wrong?

 

[andysgirl8800]

This security thing seems like such a nightmare. All the posts I read concerning the issue seem so twisted and confusing. No where can I find detailed step-by-step guides to setting it up. I'm fishing in murky waters with nothing but a hook trying to track down all the various components required to secure this database.

For example, I find frequent mention of custom shortcuts to the custom workgroup file, but despite my searches, can't find instructions on exactly WHERE to put this shortcut, or how to program it.

I'm not sure how to "split" my DB, as I was advised and a search on THAT topic gives me confilicting information. Some of the posts tell me to use the Access wizard but others say DON'T use the wizard! HUH!?

All I want to do is have a DB accessible to multiple users without "permissions", "cannot save", "already in use" errors. This is so frustrating to me, and I feel like I'm running in circles. Perhaps I've bitten off more than I can chew. I almost wish I could find a live chat site to address problems as they arise. I'm quickly approaching my deadline with no finish line in sight at this point. Where should I go from here?

 

[Banana]

If security is no concern and you trust your users to not 1) wreak havoc with your data or 2) go into tables or other places they really shouldn't be in, you can put off security just fo now-

To split database manually-

1) Make a backup.

2) Make a copy. Call it YourDatabase_BE.

3) In the original, delete all tables aren't lookups and needs to be shared along users (e.g. tables holding the records themselves)

4) File -> Get External Data -> Import

5) Navigate to your BE database and copy all tables you just deleted.

Voila the database is split. You only need to copy FE and distribute each one to your users.

As for security, welcome to the club. As soon as you get that solved, I'd like to know about it myself. :\ At least a splitted database will relieve you of the errors about not having exlcusive access and allow users to share it.

 

[pbaldy]

4) File -> Get External Data -> Import

A small correction. You want:

File -> Get External Data -> Link Tables

 

[Pat Hartman]

Let me summarize.
1. You don't need to secure the database just to share it.
2. If your users use other databases that are "secured", you need to create a shortcut for them to use to open your database so that they become part of the standard system.mdw workgroup. Remember, ALL Access databases are secured. This fact is not obvious because of the way Access uses the Admin account.
3. Spliting the database into a FE or BE can be done manually as was defined by Banana or by using the database splitter utility which is available from the Database Tools menu.
4. The BE must remain on the network share so that it is accessable by all users.
5. The FE may remain on the network share but it is FAR better to give each user a personal copy that will be located on his C: drive.

 

[andysgirl8800]

Alright. So I split the DB this time, kept a universal PW. Now, all users can open at the same time. When it comes to saving changes to the forms (with underlying tables) a warning pops up stating:

changes cannot be saved at this time because another user has also made changes since you opened it, would you like to discard the other user's changes, or copy your changes to the clipboard to retrieve later

How can I get around this? Is there a way to set up some sort of auto refresh or auto save that can allow users to update at the same time? I understand why this warning occurs, but it would be a royal pain every time. Any advice would be appreciated....thanks.

 

[Banana]

Hmm, you are not supposed to get that warning if it's shared properly.

Did you remember to give your users their own copy of FE linking to single BE? Two users opening the same FE would return a warning like that.

 

[Pat Hartman]

Occassionally, two users will open the same record and make changes to it. The changes of the first person to save are accepted and the other user gets this confusing message. Splitting the database didn't cause this error. You can add code to your application to trap the error if the conflict is common. Otherwise, explain to the users what the message means and tell them to discard their changes and start again.