Iterating through ADODB Fields inside a Recordset loop

Question

I am attempting to create a function that takes an ADODB Recordset and copies its data into a new recordset.

To do this, I use a do loop to move through each row of a source recordset. Inside that do loop, I need to use for each loop to move through each row's Fields collection, to capture its data.

However, VB6 seems to choke on the for each loop - it highlights the name of the for loop s iterator ("fld" in the example below) and throws a variable not defined error.

Oddly, the exact same for each loop works just fine when not placed inside a do loop.

Test case 1 (unable to define "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

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

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

Answer 1

Try two things:

1. 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.
2. Right before the "For Each" lines, create the loop variable by Dimming it:

Dim fldField As Field