Inserting an Array of Data into an Access Table

[billelev]

I have a large array of data (1000 x 40 x 3) that I am inserting into a database table. It is incredibly slow, and so I was wondering if there is a quicker way of inserting array data into a table.

Here is the code I am currently using:

    ' [Date],[Security],[Field]
    With rs
        For nSec = 0 To UBound(vtData, 2)
            For ndate = 0 To UBound(vtData, 1)
                For nfield = 1 To UBound(vtData, 3)
                    If IsNumeric(vtData(ndate, nSec, nfield)) Then
                        ' Add to results table
                        .AddNew
                        !Security = vtSec(nSec)
                        !Date = vtData(ndate, nSec, 0)
                        !FieldName = vtFields(nfield - 1)
                        !FieldData = vtData(ndate, nSec, nfield)
                        .Update
                    End If
                Next
            Next
        Next
    End With
 

[ChipR]

It may help to surround the whole process with
Access.Application.DBEngine.BeginTrans
and
Access.Application.DBEngine.CommitTrans

[billelev]

That seems to have helped a lot - thanks.

As an aside - is looping through the array the only/best way of inserting data into a table?

[ChipR]

I haven't found any alternative in vba, or vb for that matter.

[NeoPa]

Like Chip, I'm not aware of any alternative way of importing data from an array.

How did the data arrive in the first place though? It may be that the data can better be imported from there.

[billelev]

The data arrives in an array. Thanks for all your input.