Access database security

[Caro]

I know this is more of an Access question but maybe someone can help. I'm accessing an Access database via VB6 and don't want the end users tampering with it. I'm not keen on using an overall password - is the only way I can protect it via passwords or using the Access Dev kit? Does anyone know an easier/ cheaper option? any help greatly appreciated.

[VorTechS]

I just had a quick look in Access 97, and found an interesting section on creating passwords.

Use MS Access help and lookup 'NewPassword' and check out the example they have there. Seems pretty straight forward enough and will probably do what you want(?)

[Michael]

Access security is quite a large topic and there is quite a lot of reading material available. I will try to outline what we do in my company.

Secure the database using the Access security wizard. Then set up groups and users something like:

Group "Admins" containing users who can do anything with the database, such as developers, and people designated as system administrators.

Group "Enter" containing users who are authorised to enter and update data.

Group "View" containing users who are authorised to look at data but not update.

Default group "Users" containing default user "admin" with no rights at all.

THEN: For all of your tables and queries, set permissions against groups as

Admins: everything enabled
Enter: Read design, Read data, Update data, Insert data, Delete data set ON, and Modify design and Administer set OFF
View: Read design and Read data set ON, others OFF.
Users: all OFF

Set all permissions against all individual users OFF - their groups settings will take precedence (not strictly true - User and Group permissions are logically OR'ed together)

For the Database object: Set all ON for the Admins group, and just the Open/Run permission for other groups. Again, set ALL permissions OFF for all individual users.

I found this topic quite confusing and very fiddly at first, but once you get into it you can devise some quite sophisticated security methods.

If you use this method and you want to add a new user all you have to do is add him/her to the appropriate group and your security for your new user is taken care of.

If, in your VB programs you use bound controls then the security aspects take care of themselves.

Of course, you will have to tell your program, either via ADO or DAO, which "System.mdw" (workgroup file) you are using, and also incorporate a small login form to get the username and password, which you will also have to specify when opening your database.

You should use a different workgroup to the standard "system.mdw" that you get when you install Access. Best to give it a different name to avoid any confusion.

To quote a famous game show host (Bruce Forsyth I think!) "Thats all there is to it!!"

If you think this is too complicated then my advice is think again and persevere - it will be worth it in the end.