如何使用 VBA 从 MS-Excel (2010) 查询 MS-Access 表

Question

我正在尝试在 Excel 中编写一些 vba 代码来查询 Access 中的表。我为此尝试了多个代码示例，例如添加的链接，它们似乎都在“打开连接”部分失败。我尝试过使用不同的引用，但我不确定应该使用哪个，某些不同版本之间的差异是什么（即 Microsoft ActiveX 数据对象 2.0,2.1,...,6.0）或提供程序信息是什么应该是。对于提供者信息，我通常会看到类似的内容

“Provider=Microsoft.Jet.OLEDB.4.0;数据源=”

但我不确定这是否是我需要使用的，或者为什么/什么条件上面显示的提供程序字符串中的任何内容应该改变。有人可以教育我如何正确地做这类事情吗？

注意：如果可能的话，我想要一个无需下载任何其他应用程序即可工作的解决方案，并且适用于 2007 和 2010 版本的 Access 和 Excel，因为这需要在具有可能不同版本的 Office 的不同计算机上运行。

类似问题链接： Excel VBA 查询访问失败 http://www.mrexcel.com/forum/showthread.php?t= 527490

代码：

Sub asdf()

strFile = "C:\Users\bwall\Desktop\Excel Query Access Testing"

Dim cn As Object
Dim rs As Object
Dim strSql As String
Dim strConnection As String
Dim AppPath As String
Set cn = CreateObject("ADODB.Connection")
AppPath = Application.ActiveWorkbook.Path


strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & AppPath & "\Masterlist_Current_copy.accdb;"
Debug.Print strConnection
strSql = "SELECT [Neptune Number],[Description],[Manufacturer],[Manufacturer P/N] FROM [All Components];"
cn.Open strConnection
Set rs = cn.Execute(strSql)
MsgBox rs.Fields(0) & " rows in MyTable"
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub  

strConnection 值 =

Provider=Microsoft.Jet.OLEDB.4.0;数据 来源=C:\Users\bwall\Desktop\Excel 查询访问 测试\Masterlist_Current_copy.accdb;

Answer 1

如果您的目标数据库是 ACCDB 格式，则 Provider 部分必须为 Provider=Microsoft.ACE.OLEDB.12.0。 Provider=Microsoft.Jet.OLEDB.4.0 仅适用于较旧的 MDB 格式。

如果您运行的是 32 位 Windows，您甚至不需要安装 Access。 Jet 4 作为操作系统的一部分包含在内。如果您使用的是 64 位 Windows，则不包括 Jet 4，但您仍然不需要安装 Access 本身。您可以安装Microsoft Access Database Engine 2010 Redistributable。确保下载匹配的版本（AccessDatabaseEngine.exe 对于 32 位 Windows，或 AccessDatabaseEngine_x64.exe 对于 64 位）。

您可以通过使用后期绑定来避免有关 ADO 版本引用的问题，这不需要任何引用。

Dim conn As Object
Set conn = CreateObject("ADODB.Connection")

然后将 ConnectionString 属性分配给 conn 对象。下面是一个快速示例，它从 Excel 2003 中的代码模块运行，并显示一个包含 MyTable 行数的消息框。它对 ADO 连接和记录集对象使用后期绑定，因此不需要设置引用。

Public Sub foo()
    Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Access\webforums\whiteboard2003.mdb"
    strSql = "SELECT Count(*) FROM MyTable;"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    MsgBox rs.fields(0) & " rows in MyTable"
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

如果此答案无法解决问题，请编辑您的问题，向我们展示您尝试使用的完整连接字符串以及您在响应该连接字符串时收到的确切错误消息。

Answer 2

Sub Button1_Click()
Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Documents and Settings\XXXXXX\My Documents\my_access_table.accdb"
    strSql = "SELECT Count(*) FROM mytable;"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    MsgBox rs.Fields(0) & " rows in MyTable"

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

Answer 3

您所需要的只是一个 ADODB.Connection

Dim cnn As ADODB.Connection ' Requieres reference to the
Dim rs As ADODB.Recordset   ' Microsoft ActiveX Data Objects Library

Set cnn = CreateObject("adodb.Connection")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Access\webforums\whiteboard2003.mdb;"

Set rs = cnn.Execute(SQLQuery) ' Retrieve the data

Answer 4

Option Explicit

Const ConnectionStrngAccessPW As String = _"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\Users\BARON\Desktop\Test_DB-PW.accdb;
Jet OLEDB:Database Password=123pass;"

Const ConnectionStrngAccess As String = _"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\Users\BARON\Desktop\Test_DB.accdb;
Persist Security Info=False;"

'C:\Users\BARON\Desktop\Test.accdb

Sub ModifyingExistingDataOnAccessDB()

    Dim TableConn As ADODB.Connection
    Dim TableData As ADODB.Recordset


    Set TableConn = New ADODB.Connection
    Set TableData = New ADODB.Recordset

    TableConn.ConnectionString = ConnectionStrngAccess

    TableConn.Open

On Error GoTo CloseConnection

    With TableData
        .ActiveConnection = TableConn
        '.Source = "SELECT Emp_Age FROM Roster WHERE Emp_Age > 40;"
        .Source = "Roster"
        .LockType = adLockOptimistic
        .CursorType = adOpenForwardOnly
        .Open
        On Error GoTo CloseRecordset

            Do Until .EOF
                If .Fields("Emp_Age").Value > 40 Then
                    .Fields("Emp_Age").Value = 40
                    .Update
                End If
                .MoveNext
            Loop
            .MoveFirst

        MsgBox "Update Complete"
    End With


CloseRecordset:
    TableData.CancelUpdate
    TableData.Close

CloseConnection:
    TableConn.Close

    Set TableConn = Nothing
    Set TableData = Nothing

End Sub

Sub AddingDataToAccessDB()

    Dim TableConn As ADODB.Connection
    Dim TableData As ADODB.Recordset
    Dim r As Range

    Set TableConn = New ADODB.Connection
    Set TableData = New ADODB.Recordset

    TableConn.ConnectionString = ConnectionStrngAccess

    TableConn.Open

On Error GoTo CloseConnection

    With TableData
        .ActiveConnection = TableConn
        .Source = "Roster"
        .LockType = adLockOptimistic
        .CursorType = adOpenForwardOnly
        .Open
        On Error GoTo CloseRecordset

        Sheet3.Activate
        For Each r In Range("B3", Range("B3").End(xlDown))

            MsgBox "Adding " & r.Offset(0, 1)
            .AddNew
            .Fields("Emp_ID").Value = r.Offset(0, 0).Value
            .Fields("Emp_Name").Value = r.Offset(0, 1).Value
            .Fields("Emp_DOB").Value = r.Offset(0, 2).Value
            .Fields("Emp_SOD").Value = r.Offset(0, 3).Value
            .Fields("Emp_EOD").Value = r.Offset(0, 4).Value
            .Fields("Emp_Age").Value = r.Offset(0, 5).Value
            .Fields("Emp_Gender").Value = r.Offset(0, 6).Value
            .Update

        Next r

        MsgBox "Update Complete"
    End With


CloseRecordset:
    TableData.Close

CloseConnection:
    TableConn.Close

    Set TableConn = Nothing
    Set TableData = Nothing

End Sub