ACC: No Password Prompt Using Microsoft Access .MDB with ODBC

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

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

CAUSE

You have not specified a Microsoft Access workgroup information file (System.mdw in Microsoft Access version 7.0 or 97, or System.mda in version 1.x and 2.0) 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 will open your Microsoft Access database (.mdb) file without a password as a member of the Users group, with the permissions of the Users group.

RESOLUTION

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

NOTE: For Microsoft Access 1.x and 2.0, you must have the ODBC Driver Pack 2.0 in order to use these instructions. Earlier Microsoft Access ODBC drivers do not allow you to specify a workgroup information file in the setup of your ODBC data source.

For more information about obtaining the ODBC Driver Pack 2.0, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q125235
   TITLE     : INF: ODBC Driver Pack 2.0 Fulfillment Kit Q&A

1. Start Control Panel.

2. Double-click the ODBC icon.

3. In the ODBC Data Source Administrator box (or Data Sources in Microsoft Access 7.0 or earlier), select the Microsoft Access data source you are using, and then click Configure (or Setup in version 7.0 or earlier).

4. In the System Database box, click Database.

5. Click the System Database button.

6. In the Select System Database box, locate and select the appropriate workgroup information file (System.mda or System.mdw).

7. Click OK, and then close the ODBC Data Source Administrator.

MORE INFORMATION

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 Microsoft Access database without a password, you will log on to the database as a member of the Users group and will only have permissions to 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 will not be 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 may not have permissions to modify data in tables, but a user with a password may have permissions to modify data in the tables.