Select the product you need help with

How to use the OpenDatabase method to open password-protected databases in Access 2000

Article ID: 209953 - View products that this article applies to.

This article was previously published under Q209953

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

SUMMARY

This article describes how to use the OpenDatabase method to open a Microsoft Access database that has a database password. Note that this is different from opening a database that is secured with the Microsoft Access user-level security feature.

Back to the top | Give Feedback

MORE INFORMATION

If you want to use the OpenDatabase method to open a password-protected database, specify the database password as part of the Connect argument. The syntax to open a database with the OpenDatabase method is as follows:

Set db = workspace.OpenDatabase(dbname, options, read-only, connect)

NOTE: Even though the Options and Read-Only arguments of the OpenDatabase method are documented in Help as being optional arguments, you must provide them when you use the Connect argument. If you use a Connect argument and you do not provide the Options and Read-Only arguments, you receive run-time error 3031:

Not a valid password.

You receive this error message even if the password that you provided in the Connect argument is correct. If you do not need to use a Connect argument, you can omit the Options and Read-Only arguments.

When you use the OpenDatabase method to open a password-protected Access database, the Connect argument of the OpenDatabase method requires the following syntax:

MS Access;pwd=password

To use the OpenDatabase method to open the sample database Northwind.mdb (which is protected with a database password of "northwind"), follow these steps:

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Start Microsoft Access and open the sample database Northwind.mdb for exclusive access. To do so, click Open Database on the File menu, click the arrow on the Open button to display the options, and then click Exclusive Open.
On the Tools menu, point to Security, and then click Set Database Password.
Type northwind in both the Password and Verify boxes.
Click OK to close the Set Database Password dialog box.
Close the database.
Create a new, blank database.

Create a module and type the following procedure:

NOTE: Substitute the correct path to Northwind.mdb on your hard disk in the following sample code.

Sub OpenDB()
 Dim db As DAO.Database
 Dim ws As DAO.WorkSpace
 Dim rst As DAO.Recordset
 Set ws = DBEngine.WorkSpaces(0)
 Set db = ws.OpenDatabase _
 ("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
 False, False, "MS Access;PWD=northwind")
 Set rst = db.OpenRecordset("Customers", dbOpenDynaset)
 If rst.RecordCount > 0 Then
   rst.MoveLast
   MsgBox rst!CustomerID
 End If
 rst.Close
 db.Close
End Sub

To test this procedure, type the following line in the Debug window, and then press ENTER:
OpenDB
Note that a message box displays the Customer ID of the last record in the Customers table, indicating that the database was successfully opened.

Back to the top | Give Feedback

REFERENCES

For more information about the OpenDatabase method, click Microsoft Visual Basic Help on the Help menu, type opendatabase method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about database passwords, click Microsoft Access Help on the Help menu, type protect a microsoft access database (.mdb) file with a password or encryption in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Back to the top | Give Feedback