Multi-User

[Bukwee]

Can Access be set up for a multi-user enviornment across a network? If so what is the best solution?

Thank you Down time is a state of mind.

 

[JimHorton]

BukWee,
Yes, it can, though it's not client/server. Your next question--whats the best solution--was quite vague. If you're looking for the basic architecture in a broad sense, I've found that a data .mdb on the server and program .mdb's for the client located on each local machine is best for performance. Some feel that a single client .mdb on the network is best, and from an upgrade standpoint it may be, but there are methods to keep dozens of clients sync'd with the program .mdb on their local machine. The performance difference of just loading a form locally as compared to off the network is worth it, I think.
--Jim

 

[Bukwee]

Thank you for your response! I thought it would be the logical choice to place a data.mdb on a server, then access the file remotely. However, aren't there issues with multiple users accessing the same table at once? Or will the built-in security [in Access] be adequate to handle this file lock/security issues?

Also, can you set security options with user rights (read/write access)on a server only? Or do you have to set for each remote client? If I'm not mistaken, the security file remains local to the MS Access client. I hope this wasn't too confusing a question.

Thanks! Down time is a state of mind.

 

[JimHorton]

As far as the locking, that's something you have to program in--Access will put up a default message if you try to edit a locked record, or if someone else has written to a record you had open before you've saved it. You should set the global lock behaviour in the Options|Advanced tab. Then on each form you can override form-by-form, and in code when you do ado/dao you can set each recordset to have different locking behaviour.
The securtiy is in 2 places--the .mdw file (usually system.mdw) which contains basically a list of users & passwords, and in the .mdb itself, which contains the permissions for each user. So you when you set up security on the backend tables, you need to make sure that the users with rights exist in the .mdw file--which can be put on the network and in the shortcut where the user opens the .mdb, you can specify this .mdw in the command line.

Security is a big subject, but in general, if you're securing this, you should develop it under a unique user that you've created (he's the owner of all objects) and remove rights from Admin user & Admins group and Users group for ALL objects. Then create a user or group which can access the db & tables, and go from there--creating different users or groups with different rights. The key is to create the db under a non-admin userid (if it's too late, just open a new db under a new user, and import all objects, then delete the old .mdb).
--Jim

 

[Bukwee]

Thanks for your input! This totally answered my questions!! Down time is a state of mind.