ACC2000: No Password Prompt Appears When Using Microsoft Access .MDB with ODBC

You are not prompted for a user name and password as you would expect when you are using ODBC from an application to open a secured Microsoft Access database.

This behavior occurs when you have not specified a Microsoft Access workgroup information file, System.mdw, to use with the Microsoft Access ODBC data source. The workgroup information file contains a list of users, groups, and passwords.

If you do not specify a workgroup information file, ODBC opens your Microsoft Access database (.mdb) file without a password and grants you the permissions of the Users group.

Edit the Microsoft Access ODBC data source to use the appropriate Microsoft Access workgroup information file.

Follow these steps to specify a workgroup information file to use in a Microsoft Access ODBC data source:

1. Open Control Panel.
2. Double-click ODBC Data Sources (32bit).
   
   NOTE: To open the ODBC Data Source Administrator in Windows 2000, click Start, click Run, and then type odbcad32.exe in the Open box.
3. In the ODBC Data Source Administrator box, click the User DSN tab.
4. Select the MS Access Database data source, and then click Configure.
5. In the System Database box, click Database, and then click the System Database button.
6. In the Select System Database box, locate and select the System.mdw file. This file normally resides in the following location:
   C:\Program Files\Microsoft Office\Office
7. Click OK, and then close the ODBC Data Source Administrator.

If ODBC opens your Microsoft Access database without a password, this does not mean that you have circumvented Microsoft Access security.

Microsoft Access employs user-level security on databases, not share-level security. In user-level security, you grant permissions to users of your database resources, such as tables and forms. In share-level security, you password-protect your resources.

If ODBC opens your Access database without a password, you log on to the database as a member of the Users group and have permissions to only those objects in the database that are available to members of the Users group.

If members of the Users group do not have permissions to edit data in a table, you are not able to edit that table using ODBC with no System.mdw file specified.

In user-level security, the combinations of users and their permissions define database security. Information about users, groups, and passwords is stored in a workgroup information file. Information about who has permissions on database objects is stored with each database in the Microsoft Access database (.mdb) file.

Passwords are stored in the workgroup information file and are used only to prevent a user from logging on to a database as another user who has different permissions on database objects. Members of the Users group alone may not have permissions to modify data in tables, but a user with a password may be a member of other groups with permissions to modify data.