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

I am trying to write some vba code in Excel to query a table in Access. I have tried multiple code samples for this such as the added links and they all seem to fail at the "Open connection" part. I have tried using different references but I'm not sure which I should be using, what the differences are between some of the different versions (ie. Microsoft ActiveX Data Objects 2.0,2.1,...,6.0) or what the provider information should be. For the provider information I've usually been seeing something along the lines of

我正在尝试用Excel编写一些vba代码来查询Access中的表。我已经为此尝试了多个代码示例，比如添加的链接，它们在“Open connection”部分似乎都失败了。我试过使用不同的参考文献，但是我不确定我应该使用哪一个，不同版本之间的区别是什么。Microsoft ActiveX数据对象2.0、2.1、…、6.0)或提供者信息应该是什么。对于提供程序信息，我通常会看到一些类似的东西

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

”提供者= Microsoft.Jet.OLEDB.4.0;数据源= "

But I'm not sure if that is what I need to use or why/what conditions anything in the provider string shown above should change. Can someone please educate me on how to properly do this sort of thing?

但我不确定这是否是我需要使用的，也不确定上面显示的提供程序字符串中的任何条件应该更改。有人能教我如何正确地做这种事吗?

Note: If at all possible I would like a solution that would work without having to download any other application and would work for both 2007 and 2010 versions of Access and Excel since this will need to run on different computers with possibly different versions of office.

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

链接到类似的问题:excelvba查询访问失败:http://www.mrexcel.com/forum/showthread.php?

Code:
Sub asdf()

代码:子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 value =

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\bwall\Desktop\Excel Query Access Testing\Masterlist_Current_copy.accdb;

数据来源=C:\用户\bwall\桌面\Excel查询访问测试\Masterlist_Current_copy.accdb;

2 个解决方案

#1

The Provider piece must be Provider=Microsoft.ACE.OLEDB.12.0 if your target database is ACCDB format. Provider=Microsoft.Jet.OLEDB.4.0 only works for the older MDB format.

如果目标数据库是ACCDB格式，那么提供者块必须是Provider= microsoft.ac . oledb .12.0。Provider=Microsoft.Jet.OLEDB.4.0只适用于旧的MDB格式。

You shouldn't even need Access installed if you're running 32 bit Windows. Jet 4 is included as part of the operating system. If you're using 64 bit Windows, Jet 4 is not included, but you still wouldn't need Access itself installed. You can install the Microsoft Access Database Engine 2010 Redistributable. Make sure to download the matching version (AccessDatabaseEngine.exe for 32 bit Windows, or AccessDatabaseEngine_x64.exe for 64 bit).

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

You can avoid the issue about which ADO version reference by using late binding, which doesn't require any reference.

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

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

Then assign your ConnectionString property to the conn object. Here is a quick example which runs from a code module in Excel 2003 and displays a message box with the row count for MyTable. It uses late binding for the ADO connection and recordset objects, so doesn't require setting a reference.

然后将ConnectionString属性分配给conn对象。下面是一个快速示例，它运行于Excel 2003中的代码模块，并显示一个带有行计数的消息框。它对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

If this answer doesn't resolve the problem, edit your question to show us the full connection string you're trying to use and the exact error message you get in response for that connection string.

如果这个答案不能解决问题，请编辑您的问题，向我们显示您正在尝试使用的完整连接字符串以及您为该连接字符串得到的确切错误消息。

#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

#1