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!