ADO Excel to Access - bulk transfer of array?

[Guest]

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!

 

[MH]

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

 

[Guest]

Excellent question, and one that I'm considering myself.

However, for the moment there are other compelling reasons why I want to do
this in Excel before I make the final decision. Any suggestions how to do
this from excel would be greatly appreciated.

 

[MH]

Is this to be done over a LAN?

If so, it may be better to get your array into a recordset and append it to
your table to take advantage of the error handling in ADO, just in case
something does go wrong at run-time. Appending each row one-at-a-time would
probably be fine if both the Excel app and the Access app were on your local
machine.

MH