vba 在 Recordset 循环中迭代 ADODB 字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22920607/
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
Iterating through ADODB Fields inside a Recordset loop
提问by tonymke
I am attempting to create a function that takes an ADODB Recordset and copies its data into a new recordset.
我正在尝试创建一个函数，该函数采用 ADODB 记录集并将其数据复制到新记录集。
To do this, I use a doloop to move through each row of a source recordset. Inside that do loop, I need to use for eachloop to move through each row's Fields collection, to capture its data.
为此，我使用do循环在源记录集的每一行中移动。在该 do 循环中，我需要使用for 每个循环来遍历每一行的 Fields 集合，以捕获其数据。
However, VB6 seems to choke on the for eachloop - it highlights the name of the for loop s iterator ("fld" in the example below) and throws a variable not definederror.
然而，VB6 似乎在for each循环中窒息- 它突出显示了 for 循环迭代器的名称（下面示例中的“fld”）并抛出一个变量未定义错误。
Oddly, the exact same for each loop works just fine when not placed inside a doloop.
奇怪的是，当不放置在do循环中时，每个循环的完全相同也能正常工作。
Test case 1 (unable to define "For Each fld"):
测试用例 1 （无法定义“For Each fld”）：
'targetTableName As String: name of new table to create 'sourceRecordSet As ADODB.Recordset: open recordset containing the results to publish to the new targetTableName
'targetTableName As String：要创建的新表的名称 'sourceRecordSet As ADODB.Recordset：包含要发布到新 targetTableName 的结果的打开记录集
Public Sub createTableFromRecordset(targetTableName As String, sourceRecordSet As ADODB.recordSet)
'(irrelevant code omitted)
'create MDB RS object
Dim targetRecordSet As ADODB.recordSet
Set targetRecordSet = mdbQuery("select * from targetTableName;")
'write data to recordset
sourceRecordSet.MoveFirst ' to be safe
targetRecordSet.MoveFirst ' to be safe
While Not sourceRecordSet.EOF
targetRecordSet.AddNew
For Each fld In sourceRecordSet.Fields 'fails here, hilighting fld
'do work
Next fld
sourceRecordSet.MoveNext
Loop
'(irrelevant code omitted)
End Sub
Test case 2 (able to define "For Each fld" just fine):
测试用例 2 （能够定义“For Each fld”就好了）：
Private Sub testCase2()
'Create a source dataset
Dim sourceRs As ADODB.Recordset
Set sourceRs = functionThatGetsRecordset("(a query)")
'Create target db conn
Dim mdbConn As ADODB.Connection
Set mdbConn = functionThatGetsConn()
'iterate through source's fields
For Each fld In sourceRs.Fields 'works fine
'do work
Next fld
End Sub
回答by Blackhawk
Try two things:
尝试两件事：
- Use a different variable name just to be safe. "Field" is the name of a type, and has the potential to confuse both you and VBA. Instead, I would suggest using "fldField" or "tempField" or some such.
- Right before the "For Each" lines, create the loop variable by Dimming it:
- 使用不同的变量名只是为了安全。“字段”是一种类型的名称，可能会混淆您和 VBA。相反，我建议使用“fldField”或“tempField”等。
- 在“For Each”行之前，通过将其变暗来创建循环变量：
Dim fldField As Field
Dim fldField As Field
