您的位置: 首页  >  技术问答  >  如何使用VBA从MS-Excel（2010）查询MS-Access表

如何使用VBA从MS-Excel（2010）查询MS-Access表

分类: 技术问答 • 2022-03-15 14:02:51

如何使用VBA从MS-Excel（2010）查询MS-Access表

问题描述：

我正在Excel中编写一些vba代码来查询Access中的一个表。我已经尝试了多个代码示例，例如添加的链接，并且它们似乎都在打开连接部分失败。我已经尝试使用不同的引用，但我不知道我应该使用什么，一些不同的版本（即Microsoft ActiveX数据对象2.0,2.1，...，6.0）或提供者信息之间的区别是什么应该。对于提供者信息，我通常会看到一些沿着

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


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

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

但我不知道这是否需要使用，为什么/什么条件在提供者字符串中显示以上应该改变。有人可以教我如何正确地做这样的事情？

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?

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

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.

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

Links to similar questions: Excel VBA query to access is failing http://www.mrexcel.com/forum/showthread.php?t=527490

代码

Sub asdf（）

Code:
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 value =

strConnection value =


提供者= Microsoft.Jet.OLEDB.4.0;数据
源= C：\Users\bwall\Desktop\Excel查询访问
测试\Masterlist_Current_copy.accdb;

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


答

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

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.

您不应该甚至如果您正在运行32位Windows，则需要安装Access。 Jet 4作为操作系统的一部分。如果您使用的是64位Windows，则不包括Jet 4，但您仍然不需要安装Access本身。您可以安装 Microsoft Access数据库引擎2010可再分发。确保下载匹配的版本（32位Windows的AccessDatabaseEngine.exe或64位的AccessDatabaseEngine_x64.exe）。

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).

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

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

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

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

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.

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.

相关推荐