Copy ADO Recordset using loop

Question

I tried and failed to edit records from an ADODB recordset that I populate with an SQL (Original Question. So then I decided to go the old fashioned (and inefficient) way and copy the recordset onto a fresh new one record by record.

I start by setting the field properties equal (Data Type and Size), since I want to make sure I get a correct data match. However, I encounter two errors:

"Non-nullable column cannot be updated to Null"

and

"Multiple-step operation generated errors. Check each status value"

(Which was exactly what I was trying to avoid by looping!)

Here is the code:

'Create recordset
Set locRSp = New ADODB.Recordset

'Copy fields (same data type, same size and all updateable (which is the final goal)
For Each Field In locRS.Fields
    locRSp.Fields.Append Field.Name, Field.Type, Field.DefinedSize, adFldUpdatable
Next

'Copy records
locRSp.Open
locRS.MoveFirst
'Loop original recordset
Do While Not locRS.EOF
    locRSp.AddNew
    'Loop all fields
    For Each Field In locRS.Fields
        locRSp.Fields(Field.Name) = locRS.Fields(Field.Name)
    Next
    locRS.MoveNext
Loop

What I dont understand is:

If I am copying the original field properties (Size and Type), why would it give data errors!?

Is there some other property I need to be looking at? How?

score 0 · Accepted Answer · answered Sep 05 '14 at 15:28

For the first problem: Simply, if you want to store Null values, you need to set the attribute to "adFldIsNullable"

So for my example I changed the append call to:

locRSp.Fields.Append Field.Name, Field.Type, Field.DefinedSize, adFldIsNullable

For the second problem: When the query is downloaded to the original recordset the field properties are set I guess depending on the data itself. But in this case, I went one by one investigating what that was and found that the problem column was set to:

Data Type adNumeric

Which needs to have a precision and scale defined. Where precision is how many digits you want, and scale is number of decimals

So in my case I added an IF to the loop that copies the fields:

If Field.Type = 131 Then '131 is the constant value for adNumeric
    'Define Scale
    locRSp.Fields(Field.Name).NumericScale = 0
    'Define Precision
    locRSp.Fields(Field.Name).Precision = 4
End If

Copy ADO Recordset using loop

1 Answers1