Copying data from an array to an Access table

Question

Background

I am writing a conversion using MS Access.  I have 120 columns I have to run through an data edit, scrubing program.  I have create a table by field number (1st field, 2nd field, 3rd field, etc) that has all of the specs for editing the data.  Some edits are generic such as verify the field is an integer, date, phone number, etc field.  Some edits are actually conversion - such as change a Y/N field to a 1/0 field.  Of a full name "End Cap" to a code of "EC".

I use the GetRow method to read in the record, get a count of the fields, and then loop through the array and edit the field based upon it position in the row and the edit specs.  This is done by field number (1st, 2nd, 3rd, etc).

Ideally, I would like to read the read in from the source field, edit it / scrub the data, and then write it out to a new table.

Given the sample code below in my question, I also wonder if I should have just done aMyRecordset**.**Fields.Count and  MoveNext to loop through the row using rsR.Fields(f).Value.  The only thing is, I use the same routine to edit / data scrub about 15 different files.  And each file has a different number of fields.  I used the GetRows to get the number of fields using the following code:

    Do Until GetRowsOK(rs, arCurrRow) = False

        lngNoCols = UBound(arCurrRow, 1)               ' * ,1 = curr data row

        For lngColNo = 1 To lngNoCols

            strCurrData = Nz(arCurrRow(lngColNo, 0), "")

            Call EditTheDataFile(lngRowNo, lngColNo, strCurrData, arFldSpecs, _

                 rsDEE)

        Next lngColNo

        lngRowNo = lngRowNo + 1

        DoEvents

    Loop

Question

How do I write the array out to the destination table?  There does not appear to be PutRow method. I did see the below sample. Is this the best way to write the data back out to a new table?

With rsR

For j = 0 to UBound(Ar,0) 'loop through records

   .AddNew

   For f = 0 to UBound(Ar,1) 'loop through fields

        rsR.Fields(f).Value = Ar(j,f)

   Next r

   .Update

Next j

Thanks,

Dennis

Answer 1

Most, if not all of your edits would be handled quicker and more efficiently using an update query or append query.   Also, if your source table contains a large recordset, you might encounter problems attempting to load it to an array.