access in multi-user environment (1 Viewer)

[presmike]

Ok, i have a database that is used in a multiuser environment. As i understand you can replicate it or use a sql server so that users can make changes to the database even when others are on it. My question is this: can i automate the synchronization of the replicas? If not i guess i have to look in to sql server. Thanks for any help you can offer or if you have a better way of doing it i am all ears.

Sincerely

Michael Murphy
Network Admin
Empire Construction

 

[The_Doc_Man]

If you have a networking environment you can let your users open the database from a shared location. No synchronization at all. However, the down side of this is the need to set up workgroups to enforce the sharing.

Issues in taking this approach:

1. Users need rights to the shared directory. They will have to be able to create and delete files, modify files, read files, update files, etc. (The files they MIGHT create or delete would be .LDB files used for file locking purposes.)

2. You need to read up on workgroup security to see how to set up protection on certain things you want to protect.

3. If you DO take this approach, try to divide your users into functional groups - i.e. read-only, can add data, can change data, can remove data, etc. Define some groups with convenient names for these functions. Then when you add users, assign them to groups as needed. A user can be in more than one group so there is no problem here.

4. Don't name your workgroup file SYSTEM.MDW - name it something else. Anything else. Put IT in the shared directory too.

5. Access workgroups include default groups Admins and Users as well as default user Admin. (No 's' on the username.) Not only that, you cannot get rid of them. But you CAN control them. So this is what you do.

DO NOT ASSIGN ANYONE TO THESE GROUPS. Build your own group that has full admin rights. Build your own separate admin account. Assign it to your admin group. Then revoke all rights except perhaps Read-Only to both Admins group and Users group. (Or take away all rights completely.) This will block depredation caused by folks who don't bother to join the workgroup first.

 

[presmike]

Thanks for the reply. the database is a production report that all users (30 people) need to be able to add to. The thing i am running into is if more than one is in the database at one time the others can't save to it. Maybe this clarifies things a bit. I am not at all worried about security, just that they can edit and save at any give time no matter who else is in the database.

 

[Pat Hartman]

You don't need to use Access security for shared databases unless your application requires it. It is best however, to split the tables out into their own database and then link to them from the database that contains all the application objects (forms, reports, queries, etc.). If you don't know how to do this yourself, you can use the database splitter wizard to do it for you. As with anything else, backup first! Make sure that the users dont' open the database in exclusive mode. Also make sure that they all have create and delete rights to the network directory where the database resides. This is necessary because Access uses an .ldb file to track database activity and when the first person opens the database, Access needs to "create" the .ldb file. Then when the last person closes the database, Access needs to "delete" the .ldb file.

Newer versions of Access, A2K and A2002, will only allow a single user in a database to update any database objects (forms, reports, queries, etc.). If the database is already in use by someone who has updated an object, no further users will be allowed access. If your application changes objects on the fly or you allow your users to update objects, you'll need to give EVERY user their own copy of the front end database. They can still share the data database on the server.