CODEWITHSUNDEEP
excelms-accessvba
Arane
Arane

Reputation: 323

Excel VBA querying from Password-Protected Access Database

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

Upvotes: 3

Views: 3874

Answers (2)

Santosh
Santosh

Reputation: 12353

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

Upvotes: 2

PaichengWu
PaichengWu

Reputation: 2689

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

Upvotes: 0

Related Questions