Why is it generated in Excel if you want it in Access?

Would it not make more sense to run the code which generates the array in
Access and instead of populating an array, use the table you want the info
in?

MH

"Post Tenebras Lux" wrote in
message ...
I am struggling to use a SQL command to send data via ADO that I have in an
array over to an existing and empty table in Access. Because my data is
large (and produced at runtime), it is in an array. I can easily convert
the
array data to a recordset (in Excel).

I can do single record updates looping through each row of the array using
the adCmdTable option

strAccessTable = "RAWDATA"

rst_DB.Open strAccessTable , cnn_DB, adOpenKeyset,
adLockBatchOptimistic, adCmdTable

With rst_DB
'loop
.AddNew
.Fields(1).value = MyArrayInExcel(i,1)
.Fields(2).value = MyArrayInExcel(i,2)
.Fields(3).value = MyArrayInExcel(i,3)
.update
'loop
end with
'This works fine.


I would like to find a way to transfer the entire array or recordset over
at
one time. All the SQL statements that I've seen permit either a worksheet
or
a named reference in Excel to replace the data Table.

The following results in an error message that the "input table or
worksheet
cannot be found"

Set cnn_DB = New ADODB.Connection
cnn_DB.Open DbConnection

Set cmd_DB = New ADODB.Command
Set cmd_DB.ActiveConnection = cnn_DB


Set rst_DB = New ADODB.Recordset
rst_DB.ActiveConnection = cnn_DB

strAccessTable = "RAWDATA"

strArray = "MyArrayInExcel"
strSQL = ""
strSQL = strSQL & " INSERT INTO " & strAccessTable & " SELECT * "
strSQL = strSQL & " FROM " & strArray

rst_DB.Open strSQL, cnn_DB, adOpenStatic, adLockBatchOptimistic,
adCmdText


With cmd_DB
.CommandText = strSQL
.CommandType = adCmdText
.Execute
End With

I've tried putting the array data into a recordset object in Excel, and
then
trying to send it via the SQL, but it won't execute - "can't find input
table..."

I'm sure there is a way using the insert into SQL command, if I could
only
find an source object / datastructure acceptable to ADO. To keep it fast,
I
don't want to put the data into a worksheet (it probably wouldn't fit).

Thanks in advance!