如何使用 VBA 从 MS-Excel (2010) 查询 MS-Access 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7842607/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to query a MS-Access Table from MS-Excel (2010) using VBA
提问by Bryan
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 中的表。我为此尝试了多个代码示例，例如添加的链接，但它们似乎都在“打开连接”部分失败。我尝试使用不同的引用，但我不确定应该使用哪个，某些不同版本（即 Microsoft ActiveX Data Objects 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 版本的不同计算机上运行。
Links to similar questions: Excel VBA query to access is failinghttp://www.mrexcel.com/forum/showthread.php?t=527490
类似问题的链接： Excel VBA query to access is failed http://www.mrexcel.com/forum/showthread.php?t=527490
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 值 =
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\bwall\Desktop\Excel Query Access Testing\Masterlist_Current_copy.accdb;
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\bwall\Desktop\Excel Query Access Testing\Masterlist_Current_copy.accdb;
回答by HansUp
The Provider piece must be Provider=Microsoft.ACE.OLEDB.12.0if your target database is ACCDB format. Provider=Microsoft.Jet.OLEDB.4.0only works for the older MDB format.
Provider=Microsoft.ACE.OLEDB.12.0如果您的目标数据库是 ACCDB 格式，则Provider 部分必须是。 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，您甚至不需要安装 Access。Jet 4 包含在操作系统中。如果您使用的是 64 位 Windows，则不包括 Jet 4，但您仍然不需要安装 Access 本身。您可以安装Microsoft Access Database Engine 2010 Redistributable。确保下载匹配的版本（32 位 Windows 的 AccessDatabaseEngine.exe 或 64 位的 AccessDatabaseEngine_x64.exe）。
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 中的代码模块运行，并显示带有 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
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.
如果此答案不能解决问题，请编辑您的问题以向我们显示您尝试使用的完整连接字符串以及您在响应该连接字符串时收到的确切错误消息。
回答by beginer
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
回答by agcala
All you need is a ADODB.Connection
您只需要一个 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
回答by ronieson
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
