Excel VBA querying from Password-Protected Access Database

Question

I am currently trying to query one of the table from Microsoft Access Database (.mdb), however, when I try to do a SELECT * FROM myTable, it gives an "User-defined type not defined". May I know why?

Here's my sample code:

Private Sub CommandButton1_Click()
    Dim db As DAO.Database
    Dim dbPath As String
    Dim aQuery As String
    Dim pword As String
    Dim rs As DAO.Recordset

    dbPath = ThisWorkBook.Path & "\Database.mdb"
    pword = "password"
    aQuery = "SELECT * FROM myTable"

    Set db = Access.DBEngine.Workspaces(0).OpenDatabase(dbPath, True, False, ";PWD=" & pword)
    Set rs = db.Execute(aQuery)
    rs.MoveFirst
    MsgBox rs.Fields(0)

End Sub

Answer 1

Using ADO

Add Reference: Microsoft ActiveX Data Objects 2.8 Library

---

Sub test()

    Dim Conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim dbPath As String
    Dim aQuery As String
    Dim pword As String
    Dim strcon As String


    dbPath = ThisWorkbook.Path & "\Database.mdb"
    pword = "abcd"
    aQuery = "SELECT * FROM myTable"

    strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=" & dbPath & ";" _
            & "Jet OLEDB:Database Password=" & pword & ";"

    Conn.Open strcon
    rs.Open aQuery, Conn

    If Not (rs.EOF And rs.BOF) Then
        MsgBox rs.Fields(0)
    End If

    rs.Close
    Set rs = Nothing
    Set Conn = Nothing

End Sub

Using DAO
Add Reference: Microsoft DAO 3.6 Object Library

As @Tim highlighted you have missed adding the reference to library.

Sub test()

   Dim db As DAO.Database
    Dim dbPath As String
    Dim aQuery As String
    Dim pword As String
    Dim rs As DAO.Recordset

    dbPath = ThisWorkbook.Path & "\Database.mdb"
    pword = "abcd"
    aQuery = "SELECT * FROM myTable"


    Set db = OpenDatabase(dbPath, True, False, ";PWD=" & pword)
    Set rs = db.OpenRecordset(aQuery)
    rs.MoveFirst
    MsgBox rs.Fields(0)

 End Sub

Answer 2

try this:

Private Sub CommandButton1_Click()
    Dim db As object, rs as object
    Dim dbPath As String
    Dim aQuery As String
    Dim pword As String, uid as string

    dbPath = ThisWorkBook.Path & "\Database.mdb"
    pword = "password"
    uid = "myid"

    set db = createobject("adodb.connection")
    with db.open
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & _
        ";User ID=" & uid & ";Jet OLEDB:Database Password=""" & pword & """;"
    end with
    aQuery = "SELECT * FROM myTable"

    Set rs = db.Execute(aQuery)
    rs.MoveFirst
    MsgBox rs.Fields(0)

    db.close
    set rs = nothing
    set db = nothing
End Sub