Here's an example of what SiberBob suggested.
Dim r, f , iRecCount as integer
Dim vArray as Variant
Dim rec as ADODB.Recordset
Set rec = ADODB.Recordset
rec.Open "TableName",CurrentProject.Connection,acOpenDynaset, acLockOptimistic
iRecCount = rec.RecordCount
vArray = rec.GetRows(iRecCount)
rec.Close
The GetRows method, extracts ALL the fields from your table,
the argument passed, tells how many records to extract.
I asked for all(rec.RecordCount).
vArray is a 2 dimensional array(Get rows is always 2 dim.)
vArray(a,b) a is the fields, b are the rows(or records).
To get the results from vArray, you loop through both dimensions.
Dim iFieldCount,iRowCount as integer
iFieldCount = UBound(vArray,1) gives count of fields(1st dimension)
iRowCount = UBound(vArray,2) gives count of Records(2nd dimension)
First we loop through 1st record
For r = 0 to iRowCount 'hold on first record,loop through all fields
For f = 0 to iFieldCount
In here you write code to append new record.(Whatever method you use...Insert Query, ADO AddNew ...)
txtNewField = vArray(f,r)
Next f
Next r
This sequence will give all fields in first record, then go to next record & show all fields. So when you append, remember the order your results
are coming in. Fields First & then rows & exactly how your original table had their ordinal positions.
..prepare your new table accordingly.
Hope this helps, good luck!