locked
Changing password programatically RRS feed

  • Question

  • I'm trying to change a password for a .mde file programatically. I used the code provided by Mircosoft under section 

    Exploring Microsoft Access Security 

    but it doesn't work and always gives me an error message that "Sub or Function not defined". I tried to use the code with a .mdb fil and the same message appears.

    What is the problem

    Wednesday, January 18, 2012 2:11 AM

Answers

  • Hi hanilabeb

    I just realized you are not familiar with VBA at all, correct? Else you would see what went wrong and would know how to debug errors.

    To debug the first step is always to compile the application. Open the VBA editor and then in the Debug menu select Compile to compile your application. You then should see the line in error. Place the cursor on the method that (probably AlterPassword) that caused the error and press F1. You now should see what parameters are required. Correct your statement and try to compile again. Your application should compile without error.

    Now you can put a stop (or breakpoint) to your code, call it and then go through it step-by-step until you get the error. In the immediate window (and mostly also by hoovering the variables) you can query the variables by entering
    ? Variablename
    followed by enter. Then you can see if the variables are set correctly.

    Now to the function itself. The source as you posted it will not work like this. You first have to change some values that are corresponding to your application or fix the code that it is using the parameters passed to the function. Also keep the function public and in a standard class modul so you can call it from everywhere, not only in the class or module it is placed in.

    Frankly, the code, as you have got it, is rubbish or not finished. Somebody at Microsoft should fix it. Maybe you can place a link in here so they can find it easier. 

    You have to replace [your new password] and [your old password] by the parameters that are passed to the function. In Addition you also have to replace [your path] with the corresponding parameter.
    So by now try following. Replace the line

    strAlterPassword = "ALTER DATABASE PASSWORD [Your NewPassword] [Your OldPassword];"

    by

    strAlterPassword = "ALTER DATABASE PASSWORD '" & NewPassword & "' '" & OldPassword & "';"

    and replace the line 

    .Open "Data Source=[Your Path];"

    by

    .Open "Data Source=" & Path & ";"

    Then try it again.

    Don't forget: To call this function you have to pass it the 3 parameters: OldPassword, NewPassword and Path. You may call the function (when public) in the immediate window exactly the same way you examine a variable. Intellisense should then give you the parameters you have to enter. It should look similar to following:

    ? ChangeDBPassword("foo", "bar", "c:\data\test.mdb")

    Good luck

    Henry



    Thursday, January 19, 2012 3:48 AM

All replies

  • In the VBA editor, choose Debug > Compile, and the compiler will point out the line that does not work. Perhaps that will make the problem self-evident, but if not post the code fragment for us to look at.

     


    -Tom. Microsoft Access MVP
    Wednesday, January 18, 2012 3:16 AM
  • Thanks, Tom. I tried but nothing happened. Here is the code I'm trying to use:

    Private Function ChangeDBPassword(ByVal OldPassword As String, _
            ByVal NewPassword As String, ByVal Path As String) As Boolean
        Dim objConn As ADODB.Connection
        Dim strAlterPassword As String
    
        On Error GoTo ChangeDBPassword_Err
    
        ' Create the SQL string to change the database password.
        strAlterPassword = "ALTER DATABASE PASSWORD [Your NewPassword] [Your OldPassword];"
    
        ' Open the secured database.
        Set objConn = New ADODB.Connection
        With objConn
            .Mode = adModeShareExclusive
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .Properties("Jet OLEDB:Database Password") = "OldPassword"
            .Open "Data Source=[Your Path];"
    
            ' Execute the SQL statement to change the password.
            .Execute (strAlterPassword)
        End With
    
        ' Clean up objects.
        objConn.Close
        Set objConn = Nothing
    
        ChangeDBPassword = True
    
    ChangeDBPassword_Err:
        MsgBox Err.Number & ":" & Err.Description
        ChangeDBPassword = False
    End Function


    Wednesday, January 18, 2012 4:18 AM
  • Hi Hanilabeb
     
    Move this function into a Standard Module and change it from Private to Public scope
     
    BTW: Are you sure you are using ADODB, not DAO? The code you mentioned below works only if you have a reference to ADO, default in Access is DAO. The code you have below is made to "treat" an MDB/MDE from external.
    You would have to change some parts to switch over to DAO
     
    HTH
    Henry

    Thanks, Tom. I tried but nothing happened. Here is the code I'm trying to use:

    Private Function ChangeDBPassword(ByVal OldPassword As String, _
            ByVal NewPassword As String, ByVal Path As String) As Boolean
        Dim objConn As ADODB.Connection
        Dim strAlterPassword As String
    
        On Error GoTo ChangeDBPassword_Err
    
        ' Create the SQL string to change the database password.
        strAlterPassword = "ALTER DATABASE PASSWORD [Your NewPassword] [Your OldPassword];"
    
        ' Open the secured database.
        Set objConn = New ADODB.Connection
        With objConn
            .Mode = adModeShareExclusive
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .Properties("Jet OLEDB:Database Password") = "OldPassword"
            .Open "Data Source=[Your Path];"
    
            ' Execute the SQL statement to change the password.
            .Execute (strAlterPassword)
        End With
    
        ' Clean up objects.
        objConn.Close
        Set objConn = Nothing
    
        ChangeDBPassword = True
    
    ChangeDBPassword_Err:
        MsgBox Err.Number & ":" & Err.Description
        ChangeDBPassword = False
    End Function


    Wednesday, January 18, 2012 7:14 AM
  • Thanks Henry,

    I'm using ADO and when I changed the function to Public, a new error message appeared: "Compile error: Argument not optional"

    How can I make this code works??

    Wednesday, January 18, 2012 12:04 PM
  • Hi hanilabeb

    I just realized you are not familiar with VBA at all, correct? Else you would see what went wrong and would know how to debug errors.

    To debug the first step is always to compile the application. Open the VBA editor and then in the Debug menu select Compile to compile your application. You then should see the line in error. Place the cursor on the method that (probably AlterPassword) that caused the error and press F1. You now should see what parameters are required. Correct your statement and try to compile again. Your application should compile without error.

    Now you can put a stop (or breakpoint) to your code, call it and then go through it step-by-step until you get the error. In the immediate window (and mostly also by hoovering the variables) you can query the variables by entering
    ? Variablename
    followed by enter. Then you can see if the variables are set correctly.

    Now to the function itself. The source as you posted it will not work like this. You first have to change some values that are corresponding to your application or fix the code that it is using the parameters passed to the function. Also keep the function public and in a standard class modul so you can call it from everywhere, not only in the class or module it is placed in.

    Frankly, the code, as you have got it, is rubbish or not finished. Somebody at Microsoft should fix it. Maybe you can place a link in here so they can find it easier. 

    You have to replace [your new password] and [your old password] by the parameters that are passed to the function. In Addition you also have to replace [your path] with the corresponding parameter.
    So by now try following. Replace the line

    strAlterPassword = "ALTER DATABASE PASSWORD [Your NewPassword] [Your OldPassword];"

    by

    strAlterPassword = "ALTER DATABASE PASSWORD '" & NewPassword & "' '" & OldPassword & "';"

    and replace the line 

    .Open "Data Source=[Your Path];"

    by

    .Open "Data Source=" & Path & ";"

    Then try it again.

    Don't forget: To call this function you have to pass it the 3 parameters: OldPassword, NewPassword and Path. You may call the function (when public) in the immediate window exactly the same way you examine a variable. Intellisense should then give you the parameters you have to enter. It should look similar to following:

    ? ChangeDBPassword("foo", "bar", "c:\data\test.mdb")

    Good luck

    Henry



    Thursday, January 19, 2012 3:48 AM