none
Error while connecting Access 2010 with VBA RRS feed

  • Question

  • Hi everyone , 

    I am trying to connect Access 2010 ( i.e. database with extension .accdb ) with VBA. The issue is whenever i password protect the MS Access database, only that time i am not able to connect with the connection string. if i remove password from Access DB , it works fine. I need to use connection string with password. I read all related thread and also downloaded few driver stuff , still it didn't help . 

    FYI .. i m using Win 7 ( 32 bit ) and office 2010.

    . Below is the code , if anyone can provide their expertise.

    '----------------------------------------------------------------------------------------------------------------------CODE }

    Public Const DBFullName = "D:\TEST DB\NEWDB.accdb"

    Public Sub TestDBconn()
    Dim Cnct As String, Src As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset
    Dim empidn As Integer

       Set Connection = New ADODB.Connection
        Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
        Cnct = Cnct & "Data Source=" & DBFullName & ";Jet OLEDB:Database Password=111;"
        Connection.Open ConnectionString:=Cnct

        empidn = 0

    Set Recordset = New ADODB.Recordset
                  With Recordset
                       Src = "Select ID,Name from EMP"
                       .Open Source:=Src, ActiveConnection:=Connection

                        If .EOF Then
                            Debug.Print "No records"

                            Exit Sub
                        End If

                      MsgBox Recordset.Fields(1).Value
                       Set Recordset = Nothing
              End With
    End Sub

    '----------------------------------------------------------------------------------------------------------------------CODE }                                                            

    thanks .

    • Moved by Amanda Zhu Wednesday, April 3, 2013 6:23 AM
    Tuesday, April 2, 2013 5:15 AM

Answers

  • Hi PD007,

    After some trial and error, it turns out that we need to change the default setting in the Access database.

    First of all, remove the password, and then go into Access->File->Options->Client Settings->Advanced and check "use legacy encryption". Now we can recreate the password and give it a try.

    I'm not sure why the default encryption of the Access database setting cause the behavior as we've experienced. I will report it internally.

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by PD007 Thursday, April 4, 2013 6:53 PM
    Thursday, April 4, 2013 6:15 AM
    Moderator

All replies

  • Hello,

    I have moved this thread to Access for Developers forum for better response.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, April 3, 2013 6:24 AM
  • Hi PD007,

    Welcome to the MSDN forum.

    The code in the original post works fine on my computer.

    Public Const DBFullName = "F:\00-Access\TestProducts.accdb"
    
    Sub TestOpenPasswordDB()
        Dim Cnct As String, Src As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset
    Dim empidn As Integer
    
       Set Connection = New ADODB.Connection
        Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
        Cnct = Cnct & "Data Source=" & DBFullName & ";Jet OLEDB:Database Password=123456;"
        Connection.Open ConnectionString:=Cnct
        
        Set Recordset = New ADODB.Recordset
                  With Recordset
                       Src = "Select * from Products"
                       .Open Source:=Src, ActiveConnection:=Connection
                       
                        If .EOF Then
                            Debug.Print "No records"
                            Exit Sub
                        End If
    
                      MsgBox Recordset.Fields(1).value
                       Set Recordset = Nothing
              End With
    End Sub

    Here I only changed the DB's path, password, and the sql statement.

    Please make sure that the password is ok.

    If there is any error message, please provide the detail information for us. For example, tell us the line shows the error and the content of the message.

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, April 3, 2013 8:57 AM
    Moderator
  • Thanks Amanda :) 
    Wednesday, April 3, 2013 7:09 PM
  • Thank You Yoyo Jiang.

    The modification done by you in the code worked in office 2007 but when i tried in office 2010 ( i.e. Access 2010 & Excel 2010 ) the result is something weird. The error says "Run-time-error '-2147217843 (80040e4d)' " "Not a valid password."

    The password provided in the code works individually to open access file. 

    Thanks all for the help.

    Wednesday, April 3, 2013 7:51 PM
  • Hi PD007,

    After some trial and error, it turns out that we need to change the default setting in the Access database.

    First of all, remove the password, and then go into Access->File->Options->Client Settings->Advanced and check "use legacy encryption". Now we can recreate the password and give it a try.

    I'm not sure why the default encryption of the Access database setting cause the behavior as we've experienced. I will report it internally.

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by PD007 Thursday, April 4, 2013 6:53 PM
    Thursday, April 4, 2013 6:15 AM
    Moderator
  • Hi Yoyo ,

    Thanks for all the help . It worked this time after changing to "legacy encryption".

    Good day .

    PD007.

    Thursday, April 4, 2013 6:56 PM