编写 Excel VBA 以从 Access 接收数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9083232/
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
Writing Excel VBA to receive data from Access
提问by thebiglebowski11
I am writing an excel application that draws from an Access database for work. When the user opens the Excel tool, a data table needs to populate one of the worksheets from the Access database that I created. I have been writing the VBA code in excel and I am receiving Run-Time Error: "429" ActiveX Component Can't Create Object.
我正在编写一个从 Access 数据库中提取工作的 Excel 应用程序。当用户打开 Excel 工具时，数据表需要填充我创建的 Access 数据库中的工作表之一。我一直在用 excel 编写 VBA 代码，但收到运行时错误：“429”ActiveX 组件无法创建对象。
The other questions are all written from Access but I believe I need this code written from the Excel workbook file. The code I have written is in the Workbook_Open()function so that the data is collected right as the user opens the file. Thanks very much for the help. BTW, I am using Access 2007 and Excel 2010.
其他问题都是从 Access 编写的，但我相信我需要从 Excel 工作簿文件编写的这段代码。我编写的代码在Workbook_Open()函数中，以便在用户打开文件时收集数据。非常感谢您的帮助。顺便说一句，我使用的是 Access 2007 和 Excel 2010。
Private Sub Workbook_Open()
'Will fill the first listbox with data from the Access database
Dim DBFullName As String
Dim TableName As String
Dim FieldName As String
Dim TargetRande As String
DBFullName = "D:\Tool_Database\Tool_Database.mdb"
Dim db As DAO.Database, rs As Recordset
Dim intColIndex As Integer
Set TargetRange = Range("A1")
Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset("SELECT * FROM ToolNames WHERE Item = 'Tool'", dbReadOnly)
' Write the field names
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
Next
' Write recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
采纳答案by Siddharth Rout
Tyler, Could you please test this code for me? If you get any error you will get a Message Box. Simply post a snapshot of the Message Box.
泰勒，你能帮我测试一下这段代码吗？如果出现任何错误，您将收到一个消息框。只需发布消息框的快照。
'~~> Remove all references as the below code uses Late Binding with ADO.
Private Sub Workbook_Open()
Dim cn As Object, rs As Object
Dim intColIndex As Integer
Dim DBFullName As String
Dim TargetRange As Range
10 DBFullName = "D:\Tool_Database\Tool_Database.mdb"
20 On Error GoTo Whoa
30 Application.ScreenUpdating = False
40 Set TargetRange = Sheets("Sheet1").Range("A1")
50 Set cn = CreateObject("ADODB.Connection")
60 cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
70 Set rs = CreateObject("ADODB.Recordset")
80 rs.Open "SELECT * FROM ToolNames WHERE Item = 'Tool'", cn, , , adCmdText
' Write the field names
90 For intColIndex = 0 To rs.Fields.Count - 1
100 TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
110 Next
' Write recordset
120 TargetRange.Offset(1, 0).CopyFromRecordset rs
LetsContinue:
130 Application.ScreenUpdating = True
140 On Error Resume Next
150 rs.Close
160 Set rs = Nothing
170 cn.Close
180 Set cn = Nothing
190 On Error GoTo 0
200 Exit Sub
Whoa:
210 MsgBox "Error Description :" & Err.Description & vbCrLf & _
"Error at line :" & Erl & vbCrLf & _
"Error Number :" & Err.Number
220 Resume LetsContinue
End Sub
回答by HansUp
Both DAO and ADO include recordset object types. However they are not compatible. Your declaration for the rsobject variable is ambiguous.
DAO 和 ADO 都包含记录集对象类型。但是，它们不兼容。您对rs对象变量的声明不明确。
Dim db As DAO.Database, rs As Recordset
A potential problem is that if your project includes a reference to ADO, and the precedence of that reference is above your DAO reference, rswill wind up as an ADO recordset rather than a DAO recordset.
一个潜在的问题是，如果您的项目包含对 ADO 的引用，并且该引用的优先级高于您的 DAO 引用，则rs最终将作为 ADO 记录集而不是 DAO 记录集。
I'm not certain this is the cause of the error you're seeing. However setting rsto db.OpenRecordset(something)should fail if rs is an ADO recordset because OpenRecordsetreturns a DAO recordset.
我不确定这是您看到的错误的原因。但是rs，db.OpenRecordset(something)如果 rs 是 ADO 记录集，则设置为失败，因为OpenRecordset返回 DAO 记录集。
I think you should change the declaration to this:
我认为您应该将声明更改为：
Dim db As DAO.Database, rs As DAO.Recordset
Even if that change doesn't resolve your problem, I encourage you to alwaysqualify the type when declaring recordset object variables ... to avoid ambiguity.
即使该更改不能解决您的问题，我也鼓励您在声明记录集对象变量时始终限定类型……以避免歧义。
And if this isn't the fix, please tell us which line of your code triggers the current error you're seeing.
如果这不是修复，请告诉我们您的哪一行代码触发了您看到的当前错误。
Here is another red flag:
这是另一个危险信号：
Dim TargetRande As String
Later you have:
后来你有：
Set TargetRange = Range("A1")
Add Option Explictto the Declarations section of your module. Then choose Debug->Compile from the VB editor's main menu. That effort will highlight misspelled variable names, and also alert you to syntax errors.
添加Option Explict到模块的声明部分。然后从 VB 编辑器的主菜单中选择 Debug->Compile。这项工作将突出显示拼写错误的变量名称，并提醒您注意语法错误。
回答by Govert
It works fine on my machine (except the field names get overwritten by the first row of data - for the field names you probably mean TargetRange.Offset(0, intColIndex)).
它在我的机器上运行良好（除了字段名称被第一行数据覆盖 - 对于字段名称，您可能指的是 TargetRange.Offset(0, intColIndex)）。
Do you have a Tools -> References... to the Microsoft DAO 3.6 Object library?
你有工具 -> 引用...到 Microsoft DAO 3.6 对象库吗？
Are you perhaps using the 64-bit version of Excel 2010 (check under File->Help in the 'About Microsoft Excel' section)? If so the old version DAO libraries won't work, and you'll need to install the 64-bit ACE DAO library, which is available for 64-bit.
您是否正在使用 64 位版本的 Excel 2010（在“关于 Microsoft Excel”部分的文件->帮助下检查）？如果是这样，旧版本的 DAO 库将无法工作，您需要安装 64 位 ACE DAO 库，该库可用于 64 位。
