HOW TO: Connect to a Security-Enhanced Microsoft Access Database by Using Microsoft Visual Studio .NET (823927)

SUMMARY

This step-by-step article describes how to connect to a Microsoft Access database by using Microsoft Visual Studio .NET in the following scenarios:

• Connecting to the database without security enabled.
• Connecting to the password-protected database.
• Connecting to the database with user-level security.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft Visual Studio .NET
• Northwind sample database in Microsoft Access

This article assumes that you are familiar with the following topic:

• Access Database

back to the top

Connect to the Access Database Without Security Enabled

You can connect to the Access database by using Microsoft JET 4.0 OLE DB Provider.

To establish a connection to the Access database that does not have the security option enabled, follow these steps:

1. Start Microsoft Visual Studio .NET.
2. On the View menu, click Server Explorer.
3. In Server Explorer, right-click Data Connections, and then click Add Connection.
4. In the Data Link Properties dialog box, click the Provider tab. In the OLE DB Provider(s) list, click Microsoft Jet 4.0 OLE DB Provider, and then click Next.
5. On the Connection tab, click the ellipsis button (...).
6. In the Select Access Database dialog box, locate the Northwind.mdb file in the following folder:

   %Program Files%\Microsoft Office\Office10\Samples

   Note If you are using Microsoft Office Access 2003, the Samples folder is located in the Office11 folder. If you are using Microsoft Office Access 2002, the Samples folder is located in the Office10 folder.
7. Click Northwind.mdb, and then click Open.
8. In the Data Link Properties dialog box, click Test Connection. If the connection succeeds, click OK in the Data Link Properties dialog box.

back to the top

Set a Password to the Access Database

You can help to protect to your database in Access by adding a password to the database. When you use a password for the database, you give the password before you open the database. Access stores the database password in an unencrypted form.

To set the password for the Northwind sample database, follow these steps:

1. Start Access.
2. On the File menu, click Open.
3. In the Open dialog box, locate the Northwind.mdb file in the following folder:

   %Program Files%\Microsoft Office\Office10\Samples

4. Click Northwind.mdb, and then click Open Exclusive in the Open list.
5. In the Microsoft Access dialog box, click Enable Macros.
6. On the Tools menu, point to Security, and then click Set Database Password.
7. In the Set Database Password dialog box, type the password in the Password text box. Type the same password in the Verify text box, and then click OK.
8. Close the Northwind sample database, and then quit Access.

back to the top

Connect to the Password-Protected Access Database

You can use Microsoft Jet 4.0 OLE DB Provider to connect to the Access database. To connect to the database, specify the password in the JET OLB DB: Database Password property. If you do not specify the database password, the connection fails and you receive the following error message: To establish a connection to the Northwind sample database with password protection, follow these steps:

1. Start Microsoft Visual Studio .NET.
2. On the View menu, click Server Explorer.
3. In Server Explorer, right-click Data Connections, and then click Add Connection.
4. In the Data Link Properties dialog box, click the Provider tab. In the OLE DB Provider(s) list, click Microsoft Jet 4.0 OLE DB Provider, and then click Next.
5. On the Connection tab, click the ellipsis button (...).
6. In the Select Access Database dialog box, locate the Northwind.mdb file in the following folder:

   %Program Files%\Microsoft Office\Office10\Samples

7. Click Northwind.mdb, and then click Open.
8. In the Data Link Properties dialog box, click the All tab.
9. Click Jet OLEDB:Database Password, and then click Edit Value.
10. In the Edit Property Value dialog box, type the password that you set for the database in the Property Value text box, and then click OK.
11. In the Data Link Properties dialog box, click OK.

back to the top

Unset the Database Password

You can remove the database password from the sample database Northwind.mdb. To do this, follow these steps:

1. Start Microsoft Access.
2. On the File menu, click Open.
3. In the Open dialog box, find the Northwind.mdb file in the following folder:

   %Program Files%\Microsoft Office\Office10\Samples

4. Click Northwind.mdb, and then click Open Exclusive in the Open list.
5. In the Password Required dialog box, type the database password, and then click OK.
6. In the Microsoft Access dialog box, click Enable Macros.
7. On the Tools menu, point to Security, and then click Unset Database Password.
8. In the Unset Database Password dialog box, type the database password, and then click OK.
9. Close the Northwind sample database, and then quit Microsoft Access.

back to the top

Create User-Level Security for the Access Database

User-level security can be provided for the Access database to help to prevent the entry of unauthorized users to the database. When user-level security is provided for the database, you can also set the security account password for that particular user account. The security password helps to prevent an unauthorized user from connecting to the database.

By default, Access assigns a blank password for the Admin user account and other new user accounts that you create in your workgroup. You can create or change your own user account password.

The Access workgroup information file stores the information of members of the workgroup, including the user passwords. Access reads the workgroup information file while opening a database to find whether the user has permissions.

Create a workgroup file (System1.mdw), and then provide user-level security by creating a user account that is named testuser, and set the password for the user account as testuser. To do this, follow these steps:

1. Start Microsoft Access.
2. On the Tools menu, point to Security, and then click Workgroup Administrator.
3. In the Workgroup Administrator dialog box, click Create.
4. In the Workgroup Owner Information dialog box, type System123 in the Name text box. In the Workgroup ID text box, type 12345, and then click OK.
5. In the Workgroup Information File dialog box, type C:\Access Files\System1.mdw as the path, and then click OK.
6. In the Confirm Workgroup Information dialog box, click OK. Click OK in the You have successfully created the workgroup information file dialog box.
7. In the Workgroup Administrator dialog box, click Join.
8. In the Workgroup Information File dialog box, click OK. Click OK in the You have successfully joined the workgroup defined by the workgroup information file dialog box.
9. In the Workgroup Administrator dialog box, click OK.
10. On the File menu, click Open.
11. In the Open dialog box, find the Northwind.mdb file in the following folder:

    %Program Files%\Microsoft Office\Office10\Samples

12. Click Northwind.mdb, and then click Open.
13. In the Microsoft Access dialog box, click Enable Macros.
14. On the Tools menu, point to Security, and then click User and Group Accounts.
15. In the User and Group Accounts dialog box, click New on the Users tab.
16. In the New User/Group dialog box, type testuser in the Name text box, and type 1234 in the Personal ID text box, and then click OK.
17. In the Available Groups list, click Admins, and then click Add>>.
18. Click the Change Logon Password tab. In the Old Password text box, type your old password.
19. In the New Password and the Verify text boxes, type the new password admin, and then click OK.
20. Close the Northwind sample database, and then quit Access.
21. Start Microsoft Access.
22. On the File menu, click Open.
23. In the Open dialog box, find the Northwind.mdb file in the following folder:

    %Program Files%\Microsoft Office\Office10\Samples

24. Select the Northwind.mdb file, and then click Open.
25. In the Logon dialog box, type testuser in the Name text box, and then click OK.
26. In the Microsoft Access dialog box, click Enable Macros.
27. On the Tools menu, point to Security, and then click User and Group Accounts.
28. In the User and Group Accounts dialog box, click the Change Logon Password tab.
29. In the New Password and the Verify text boxes, type testuser, and then click OK.
30. Close the Northwind sample database, and then quit Access.

back to the top

Connect to the User-Level Security-Enabled Access Database

You can use Microsoft Jet 4.0 OLE DB Provider to connect to the database. The Jet OLEDB:System database property is set to the path of the workgroup file. User account name testuser and password testuser are set in the User ID and Password properties. If you do not provide any one of these values, the connection fails, and you receive an error message.

To establish a connection to the Access Northwind sample database that has user-level security enabled, follow these steps:

1. Start Microsoft Visual Studio .NET.
2. On the View menu, click Server Explorer.
3. In Server Explorer, right-click Data Connections, and then click Add Connection.
4. In the Data Link Properties dialog box, click the Provider tab. In the OLE DB Provider(s) list, click Microsoft Jet 4.0 OLE DB Provider, and then click Next.
5. On the Connection tab, click the ellipsis button (...).
6. In the Select Access Database dialog box, find the Northwind.mdb file in the following folder:

   %Program Files%\Microsoft Office\Office10\Samples

7. Click Northwind.mdb, and then click Open.
8. In the Data Link Properties dialog box, click the All tab.
9. Click Jet OLEDB:System database, and then click Edit Value.
10. In the Edit Property Value dialog box, type C:\Access Files\System1.mdw in the Property Value text box, and then click OK.
11. Click User ID, and then click Edit Value.
12. In the Edit Property Value dialog box, type testuser in the Property Value text box, and then click OK.
13. Click Password, and then click Edit Value.
14. In the Edit Property Value dialog box, type testuser in the Property Value text box, and then click OK.
15. In the Data Link Properties dialog box, click OK.
16. In the Please Enter MS JET OLE DB Initialization Information dialog box, type testuser in the Password text box, and then click OK.

back to the top