ACC2000: How to Change the Database Password Through ADO

This article was previously published under Q304915
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

SUMMARY
With Microsoft Jet version 4.0, you can change the database password by running a data-definition query. Because you can do this, it is relatively easy to set the database password through ActiveX Data Objects (ADO).

This article shows you how to set the database password, and then how to resetting the database password to a blank password.

NOTE: To set or reset the database password, you must have the database open exclusively.
MORE INFORMATION

Setting the Database Password

The following ADO example assumes that the current database has a blank database password.

NOTE: The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run properly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.

  1. Create a module, and then type the following line in the Declarationssection if it is not already there:
    Option Explicit					
  2. Type the following procedure:
     Sub ADOAddPW()   Dim cn As ADODB.Connection   Dim newpassword As String   Dim NotValid As Integer   Dim sqlExecStr As String   Set cn = CurrentProject.Connection   On Error Resume Next   ' Test to see if the database is open exclusively.   If cn.Mode <> 12 Then      MsgBox "Your database is not opened exclusively", vbCritical      Exit Sub   End If     RetryPassword:   newpassword = InputBox("Please enter new database password", "Database Password" _        , "New Database Password")' Select case for inputbox.      Select Case newpassword      ' Case where the cancel button was pressed.       Case "New Database Password"        MsgBox "No Database password set"        Exit Sub           ' Case where the OK button was pressed without entering data.       Case ""       NotValid = MsgBox("You have not entered a valid password, or clicked the cancel button" & Chr(10) & Chr(13) & _       "Do you want to change the database password?", vbCritical + vbYesNo)       If NotValid = 6 Then        GoTo RetryPassword       Else        Exit Sub       End If           'If any data is entered other than the default value.        Case Else       sqlExecStr = "ALTER Database Password " & newpassword & "``"       CurrentProject.Connection.Execute sqlExecStr       MsgBox "Database password has been set"                         End Select      End Sub					
  3. To test this function, click Run Sub/UserForm on the Run menu.
  4. Close and then reopen the database. Note that you are prompted to enter the database password.

Resetting the Database Password to a Blank Password

You can use the following code sample to reset the password to a blank database password. To set the blank password, you must use the NULL keyword. This example also assumes that the current database has a database password set as DBPassword.
  1. Create a module, and then type the following line in the Declarationssection if it is not already there:
    Option Explicit					
  2. Type the following procedure:
     Sub ResetDBPassword()   Dim cn As ADODB.Connection   Dim sqlExecStr As String   Dim ResetQuestion As Integer      Set cn = CurrentProject.Connection   On Error Resume Next      ' Test to see if the database is open exclusively.   If cn.Mode <> 12 Then      MsgBox "Your database is not opened exclusively", vbCritical      Exit Sub   End If   ResetQuestion = MsgBox("You have selected to reset the database" & _            Chr(10) & Chr(13) & "to a blank password. Do you want to continue?", vbQuestion + vbYesNo, _            "Reset Database Password")    'Reset database password based on answer to message box.        If ResetQuestion = 6 Then        sqlExecStr = "ALTER DATABASE PASSWORD NULL [DBPassword]"        CurrentProject.Connection.Execute sqlExecStr        MsgBox "Database Password has been reset."    Else        MsgBox "Database password has not been reset"        Exit Sub    End IfEnd Sub					
  3. To test this function, click Run Sub/UserForm on the Run menu.
  4. Close and then reopen the database. Note that you are not prompted to enter the old database password.
REFERENCES
For additional information about other new features of Microsoft Jet 4.0, click the article number below to view the article in the Microsoft Knowledge Base:
275561 New Features in Microsoft Jet 4.0
For more information about data-definition queries, click Microsoft Access Help on the Help menu, type what is an sql query and when would you used one? in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
inf password
Properties

Article ID: 304915 - Last Review: 12/06/2015 04:32:27 - Revision: 5.1

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbhowto KB304915
Feedback