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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Excel to Access - bulk transfer of array?
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Excel to Access - bulk transfer of array?
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" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Excel to Access - bulk transfer of array?
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 "Post Tenebras Lux" wrote in message ... 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" wrote: 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! |
Reply |
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 |