Excel VBA从密码保护的Access数据库中查询

Question

我目前正在尝试从Microsoft Access数据库(.mdb)中查询表之一，然而，当我尝试执行SELECT * FROM myTable时，它会出现"用户定义类型未定义"的错误。请问原因是什么？

以下是我的示例代码:

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

- Arane

---

你的代码在哪里出错了？是哪一行？ - PaichengWu

如果您是从Excel进行此操作，则需要添加对DAO对象库的引用。 - Tim Williams

Answer 1

使用 ADO

添加引用：Microsoft ActiveX 数据对象 2.8 库

---

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

使用DAO
添加引用: Microsoft DAO 3.6对象库

正如@Tim所指出的那样，您忘记添加对库的引用了。

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

- Santosh

Answer 2

试试这个：

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

- PaichengWu