ACC2000: How to Change the Database Password Through ADO (304915)

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 Declarations section 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 grave accent character (`) instead of the apostrophe (') character. 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 Declarations section 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 `` DBPassword"
           CurrentProject.Connection.Execute sqlExecStr
           MsgBox "Database Password has been reset."
       Else
           MsgBox "Database password has not been reset"
           Exit Sub
       End If
   
   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 not prompted to enter the old database password.