Excel VBA从受密码保护的访问数据库中查询 [英] Excel VBA querying from Password-Protected Access Database

问题描述

我当前正在尝试从Microsoft Access数据库(.mdb)查询表之一，但是，当我尝试执行 SELECT * FROM myTable 时，它给出了用户定义类型"没有定义的".我可以知道为什么吗?

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?

这是我的示例代码:

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

推荐答案

使用ADO

添加参考:Microsoft ActiveX数据对象2.8库

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

使用DAO
添加参考:Microsoft DAO 3.6对象库

Using DAO
Add Reference: Microsoft DAO 3.6 Object Library

@Tim突出显示时，您错过了添加对库的引用.

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

这篇关于Excel VBA从受密码保护的访问数据库中查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！