Writing arrays to an Access table

Question

in Excel VBA, I can easily write the content of an array to a range which is exactly tailored to the content of the array. Doing so, I can (for example) write 10.000 records in a split second to the range. If I would do so line by line, it would take ages.

This is exactly the problem I have in Access VBA.

If I want to write 10.000 array rows to a table using a DAO.Recordset object, it takes very long. The typical procedure I use is this:

Private Sub ArrayToTable()

    Dim ArrData As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    
    ReDim ArrData(1 To 2, 1 To 2)
    
    'Example array
    ArrData(1, 1) = "Paul"
    ArrData(1, 2) = 27
    ArrData(2, 1) = "Helen"
    ArrData(2, 2) = 47
    
    'Open database and create a recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblMembers", dbOpenTable)
    
    'Loop throug the array and write back to tblMembers
    With rs
    For i = 1 To UBound(ArrData, 1)
        .AddNew
        .Fields(0) = ArrData(i, 1)
        .Fields(1) = ArrData(i, 2)
        .Update
    Next i
    End With
    
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

I am optimistic that there is a better way than this, as I can manually add very many records in one go (Start/Insert/Add), which is pretty fast, too.

So here is my question: Does anyone know a method to insert the content of an array to a table in one go?

I would love to learn from your feedback.

Best regards **Ingo **