Password Protecting a MS Access file

Question

I've got a MS Access database with a table and a form, but I want the user to only see the form. I don't want them to be able to edit any of the data. The user should only be able to use the form to query the table. Any suggestions??

Answer 1

I believe you want to use Access User-Level Security. Here's the documentation at Microsoft.

Answer 2

If you are using Access 2003 or earlier, you can use user level security, as mentioned. If you are using Access 2007 you are out of luck - Microsoft in their wisdom removed this ability. You will basically have to write your own login and set permissions on editing etc. using VBA code.

Answer 3

There are a few ways to enforce some control over what users can do.

AutoExec

The most simple way is to use an AutoExec macro to initialise the user interface when the application starts.
That way to can make sure that only the form you want is displayed and hide everything else.

Runtime

A good complement to this approach is to compile your application and force the user to use the Access Runtime to use your application.
In the runtime, users don't have access to all the standard tools unless you explicitly code for it.

A good thing to know is that unlike previous versions, the Access 2007 Runtime is free, and that makes Access a very cheap platform to develop for.

Runtime emulation

With Access 2007, a simple way to ensure that the application will open as if only the runtime was installed is to change the extension of the database to .accdr.
You can also force a full Access installation to open a normal database in Runtime emulation by passing the /runtime command line switch.

Secure data-access

Note that short of encrypting the database (but then you have to manage the password), all you can do is make it hard for the user to open the tables manually.
A determined and knowledgeable user can always circumvent these protective measures and access the data.
If you need a really secure solution though, Access may not be the best choice: implementing fine grained security in Access is a greater challenge than the alternatives, say storing the data into a SQL Server database for instance where security is enforced.

Links to resources

• Security Considerations and Guidance for Access 2007 on MSDN
• A simple Microsoft Access User-Level Security Tutorial for older versions of Access.
• FormSafe, a commercial product that helps enforce security on form controls.