使用 ADO VBA 将 Excel 字段插入 Access 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11440372/
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
Using ADO VBA to Insert Excel Fields into Access Table
提问by nathansizemore
I am trying to use ADO to access and read some things from an Excel File. I understand how to get it open and do the SELECT * and put that into a Recordset Object. What I don't understand is if I am select a group of info, how to access specific fields in that Recordset.
我正在尝试使用 ADO 访问和读取 Excel 文件中的一些内容。我了解如何打开它并执行 SELECT * 并将其放入 Recordset 对象中。我不明白的是，如果我选择了一组信息，如何访问该记录集中的特定字段。
Code:
代码：
Private Sub SaveReq_Click()
'
' Saves the current entry to the database
' Into the TABLE 'pr_req_table'
'
' Open a connection to the database
dim data_base as Database
set data_base = OpenDatabase(CurrentProject.Path & "\test_database.accdb")
Sub InsertRecord()
Dim data_base As Database
Set data_base = OpenDatabase(CurrentProject.Path & "\test_database.accdb")
' Grab all information from form
' Add information to pr_req_table
Dim qd As QueryDef
Set qd = data_base.CreateQueryDef("")
qd.sql = "INSERT INTO pr_req_table(pr_no, pr_date, pr_owner, pr_link, pr_signed) " & _
"values([p1],[p2],[p3],[p4],[p5])"
qd.Parameters("p1").Value = pr_num.Value
qd.Parameters("p2").Value = Format(pr_date.Value, "mm/dd/yyyy")
qd.Parameters("p3").Value = List22.Value
qd.Parameters("p4").Value = "Excel Copy #" & elec_copy.Value
qd.Parameters("p5").Value = "Signed Copy #" & sign_copy.Value
qd.Execute
' The following section reads from the elec_copy field's hyperlink
' It scans the Excel file for items it needs to include into the table
' It enters those cells into the TABLE 'items_needed_table'
'
' Slects row by row, and if the item has been marked TRUE, inserts
' That row into the TABLE 'items_needed_table'
' Open a connection to Excel
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & elec_copy.Value & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
' Decalre a RecordSet Object
Set objRecordSet = CreateObject("ADODB.Recordset")
' Grab all Rows in the Plain_VDR Sheet where 'needed' column == TRUE
objRecordset.Open "Select line_no, desc, weeks FROM [Plain_VDR$] Where needed = TRUE", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
' Declare a loop counter for row?
Dim x as Integer
x = 0
' Write the information pulled, into the TABLE 'items_needed_table' in Access Database
Do Until objRecordset.EOF
qd.sql = "INSERT INTO items_needed_table(pr_no, line_no, desc, weeks) " & _
"Values([p1],[p2],[p3])"
' p1 was declared earlier in code, same value as before
qd.Parameters("p2").Value = objRecorset.(ROW_X, "line_no")
qd.Parameters("p3").Value = objRecordset.(ROW_X, "desc")
qd.Parameters("p4").Value = objRecordset.(ROW_X, "weeks")
qd.Execute
x = x + 1
Loop
' Close Database connection
data_base.Close
End Sub
My main point of concern is the 'Do Until' loop section. Doubtful I can insert the entire selection, because 'pr_no' is not defined in the Excel file, but back in Access Database, so I think I will need to loop that command for each row in the Excel file. What do I need to use to assign my parameters the values, per row and field, from the Recordset Object?
我的主要关注点是“执行直到”循环部分。怀疑我可以插入整个选择，因为 'pr_no' 没有在 Excel 文件中定义，而是在 Access 数据库中定义，所以我想我需要为 Excel 文件中的每一行循环该命令。我需要使用什么来为我的参数分配 Recordset 对象中每行和字段的值？
Thanks in advance for any help!
在此先感谢您的帮助！
Nathan
弥敦道
回答by Fionnuala
In your connection string, you have said HDR=Yes, which means that the first row of your range contains the names of your fields, so, very roughly:
在您的连接字符串中，您已经说过 HDR=Yes，这意味着您的范围的第一行包含您的字段名称，因此，非常粗略：
Do Until objRecordset.EOF
qd.Sql = "INSERT INTO items_needed_table(pr_no, line_no, desc, weeks) " & _
"Values([p1],[p2],[p3])"
' p1 was declared earlier in code, same value as before
'**No it was not, the earlier stuff is mostly irrelevant
qd.Parameters("p2").Value = objRecorset.Fields("line_no")
qd.Parameters("p3").Value = objRecordset.Fields("desc")
qd.Parameters("p4").Value = objRecordset.Fields("weeks")
qd.Execute
''You are moving through a recordset, not a worksheet
objRecordset.MoveNext
Loop
If this all that you are doing with the selection from Excel, it could be inserted with one query, because you are not changing pr_num.
如果这一切都是您对 Excel 中的选择所做的，则可以使用一个查询插入它，因为您没有更改 pr_num。
