Fill a table with the values of an array

[laurentc via AccessMonster.com]

Hi.

I do several calculation in a VBA code, and I use an array to keep the final
values.
That is working fine now, so I have my array full of 400 000 values (The
array is big (10000 lines and 40 columns).

Then I would like to fill in a new table with all these values.

How can I do this?

 

[laurentc via AccessMonster.com]

Hi again.

I cannot find the 'good' solution, as populating it by running 10 000 times a
"Docmd.RunSQL" query for each line is not the solution.

No one really know how to populate a table from an array?

Thanks in advance.

 

[John Spencer]

The only way I can think of to do that is
-- Open a recordset based on a table that you have already designed
-- Loop through the array, adding records to the recordset.


The following UNTESTED code example assumes that your table fields are set
up to parallel the columns in the array.

Dim rst as DAO.Recordset
Dim dbAny as DAO.Database
Dim lngCount as Long
Dim IColumn as Integer

Set DbAny = CurrentDB()
Set rst = DbAny.OpenRecordset ("SELECT * FROM YourTableName")

For LngCount = Lbound(YourArray) to UBound(YourArray)
rstAny.AddRecord
For iColumn = 0 to 39
Field(i) = YourArray(lngCount,iColumn)
Next IColumn
rstAny.Update
Next LngCount

 

[laurentc via AccessMonster.com]

John,

Thanks for your proposal.
That is looking great.
I am going to test your code, change t a little and I revert to you if needed.


Thanks again.

The only way I can think of to do that is
-- Open a recordset based on a table that you have already designed
-- Loop through the array, adding records to the recordset.

The following UNTESTED code example assumes that your table fields are set
up to parallel the columns in the array.

Dim rst as DAO.Recordset
Dim dbAny as DAO.Database
Dim lngCount as Long
Dim IColumn as Integer

Set DbAny = CurrentDB()
Set rst = DbAny.OpenRecordset ("SELECT * FROM YourTableName")

For LngCount = Lbound(YourArray) to UBound(YourArray)
rstAny.AddRecord
For iColumn = 0 to 39
Field(i) = YourArray(lngCount,iColumn)
Next IColumn
rstAny.Update
Next LngCount

Hi.

[quoted text clipped - 7 lines]

How can I do this?