从受密码保护的 Access 数据库进行 Excel VBA 查询

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

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