Add Array to Fields in RecordSet... proper way?

[ilcaa72]

i have an array and I will be filling the table with, each field independantly with a loop. This works fine. It keeps adding to the last row, and each field with no issue.

To avoid issues in future. Would this be ok, or should I make it more robust?

Issues im contemplating..
- I have read that you must move to last line before adding data, my sub works, is this necessary?
- is looping each field best way? or is there a way to enter array in 1 shot across fields that is better? thanks

Sub test()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim x As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("test")
x = Array("Mike", "Sanchez, "Teacher")

With rs
.AddNew

For i = 0 To UBound(x)
    
        rs.Fields(i + 1).Value = x(i)
        
Next

End With
        rs.Update
        rs.Close
        DoCmd.OpenTable "test"
End Sub

 

[pbaldy]

The AddNew will add a new record regardless of where you are in the recordset. I'd either open the recordset with the dbAppendOnly option or on an SQL statement that returned no records. You're pulling the entire table. Won't matter much with a small table, will with a larger one.

I can't think of a better way to populate with an array, but I rarely use them.

 

[ilcaa72]

thanks Paul, you bring up a good point about a smaller table as the recordset . thanks

 

[pbaldy]

No problemo.