none
Excel VBA to Open Password-Protected Access ACCDB (2010 version) RRS feed

  • Question

  • I found a way to get Excel to open an older .mdb using VBA.  However, I'm now using Access 2010, which is ACCDB, and I can't seem to get my Excel VBA code to insert data from the template to the Access table.  I looked at this link for guidance.

    http://answers.microsoft.com/en-us/office/forum/office_2010-access/use-dao-to-open-a-password-protected-access-2010/7193f26f-a275-e011-8dfc-68b599b31bf5?db=5&auth=1

    Based on that, I thought my code should work, but it doesn't.  Here's what I'm working with now.

    Sub Insert_Records()
        Dim conn As ADODB.Connection
        Dim myRecordset As ADODB.Recordset
        Dim strConn As String
       
        'Dim DB As DAO.Database
        'Dim ws As DAO.Workspace
        'Dim rst As DAO.Recordset
        'Set ws = DBEngine.WorkSpaces(0)
        'Set DB = ws.OpenDatabase _
        '(ThisWorkbook.Path & "\Risk_DB.accdb", _
        'False, False, "MS Access;PWD=password")
        'Set rst = DB.OpenRecordset("tblCredit_Risk", dbOpenDynaset)
     
        'Need to reference Microsoft Access 14.0 Object Library
        'Need to reference Microsoft Office 14.0 Access Database Engine

        Dim DB As DAO.Database
        Dim rec As DAO.Recordset
        Dim wrkspc As DAO.Workspace

        Set wrkspc = Access.DBEngine(0)
        Set DB = wrkspc.OpenDatabase(ThisWorkbook.Path & "\Risk_DB.accdb", False, True, "MS Access;PWD=password")
        'Set rec = DB.OpenRecordset("select * from tblCredit_Risk")
        
       'strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Risk_DB.accdb", _
            False, False, "MS Access;PWD=password")

       Set myRecordset = New DAO.Recordset
       With myRecordset
       Set rec = DB.OpenRecordset("select * from tblCredit_Risk")
          '.Open "Select * from tblCredit_Risk", _
             strConn, adOpenKeyset, adLockOptimistic
          .AddNew !rc_nr = Range("rc_nr") !rc_name = Range("rc_name") !rc_LoB = Range("rc_LoB") !rc_RBP = Range("rc_RBP") !rc_desc = Range("rc_desc") .MoveFirst .Close End With Set myRecordset = Nothing Set conn = Nothing MsgBox ("All data was successfully saved to the Risk Database!!!" & _ vbCrLf & _ vbCrLf & _ "Excel will automatically close after you click the OK Button.") Workbooks("Credit_Risk.xlsb").Save Workbooks("Credit_Risk.xlsb").Close Application.Quit End Sub

    This line throws an error.

    Set myRecordset = New DAO.Recordset

    'Compile error: Invalid use of New keyword.'

    What is wrong here?


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Monday, November 23, 2015 8:16 PM
    Monday, November 23, 2015 3:32 PM

Answers

  • Since in Access 2010 the encryption method changed for database passwords, and the ADO provider's "Jet OLEDB:Database Password" keyword does not appear to work with the new method.

    So I suggest that you could set Access->File->Options->Client Settings->Advanced and check "use legacy encryption", then recreate the password.

    • Marked as answer by ryguy72 Tuesday, November 24, 2015 1:25 PM
    Tuesday, November 24, 2015 9:21 AM

All replies

  • I don't get this.  It keeps saying not a valid filename or with not found.

    Sub Insert_Records2()
       Dim conn As ADODB.Connection
       Dim myRecordset As ADODB.Recordset
       Dim strConn As String
       
       Dim DB As DAO.Database
       Dim rec As DAO.Recordset
       Dim wrkspc As DAO.Workspace
        
       Set wrkspc = Access.DBEngine(0)
       Set DB = wrkspc.OpenDatabase("Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Risk_DB.accdb", False, True, "MS Access;PWD=password")
    
       Set myRecordset = New ADODB.Recordset
       
       Set rec = DB.OpenRecordset("select * from tblCredit_Risk")
       
       With myRecordset
          .Open "Select * tblCredit_Risk", _
             strConn, adOpenKeyset, adLockOptimistic
          .AddNew
            !rc_products = Range("rc_products")
            !rc_entity = Range("rc_entity")
          .MoveFirst
          .Close
       End With
    
       Set myRecordset = Nothing
       Set conn = Nothing
    End Sub

    I can't find anything on line that explains how this works.  It seems when you add a password onto Access, everything changes to something completely different.  The only thing that works for me is the old .mdb format.  I can go back to that, but I don't really see the point in using 12+ year old technology, when I should be able to use contemporary technology.

    Does anyone know how this stuff works?

    Thanks.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Monday, November 23, 2015 8:16 PM
    Monday, November 23, 2015 8:14 PM
  • I couldn't get it working on 2010, so I went back to 2003, and this script below works fine in that scenario.

    Sub Load_To_DB()
    
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    
        
        Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
            "Data Source=" & ThisWorkbook.Path & "\Risk_DB.mdb;" & _
                 "Jet OLEDB:Database Password=your_password_here"
        ' open a recordset
        Set rs = New ADODB.Recordset
        rs.Open "tblCredit_Risk", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
            With rs
                .AddNew ' create a new record
                ' add values to each field in the record
                !rc_nr = Range("rc_nr")
                !rc_name = Range("rc_name")
                !rc_LoB = Range("rc_LoB")
                ' add more fields if necessary...
                .Update ' stores the new record
            End With
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
        
     MsgBox ("All data was successfully saved to the Risk Database!!!" & _
     vbCrLf & _
     vbCrLf & _
     "Excel will automatically close after you click the OK Button.")
    
     Workbooks("Credit_Risk.xlsb").Save
     Workbooks("Credit_Risk.xlsb").Close
     Application.Quit
        
    End Sub
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, November 23, 2015 11:52 PM
  • Since in Access 2010 the encryption method changed for database passwords, and the ADO provider's "Jet OLEDB:Database Password" keyword does not appear to work with the new method.

    So I suggest that you could set Access->File->Options->Client Settings->Advanced and check "use legacy encryption", then recreate the password.

    • Marked as answer by ryguy72 Tuesday, November 24, 2015 1:25 PM
    Tuesday, November 24, 2015 9:21 AM
  • I knew the security changed, and presumably it got much better.  The same thing happened when we went from Windowx XP to Windows 7.  I didn't know about the trick that you described.  Thanks for bringing that to my attention!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, November 24, 2015 1:26 PM