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!

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" <(E-Mail Removed)> wrote in
message news:EE41C401-C57F-42C2-82D5-(E-Mail Removed)...
>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!

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" <(E-Mail Removed)> wrote in
> message news:EE41C401-C57F-42C2-82D5-(E-Mail Removed)...
> >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!
>
>
>

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" <(E-Mail Removed)> wrote in
message news16D527F-765F-49D3-954E-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message news:EE41C401-C57F-42C2-82D5-(E-Mail Removed)...
>> >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!
>>
>>
>>