Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Excel to Access - bulk transfer of array?
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! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to transfer emails from excel to bulk mailer | Excel Discussion (Misc queries) | |||
Bulk change cells to Array Formula | Excel Discussion (Misc queries) | |||
Access transfer to Excel | Excel Programming | |||
ADO Transfer from Excel to Access | Excel Discussion (Misc queries) | |||
Transfer data from Excel to Access | Excel Programming |