Can you use ADO from Excel to run an Append Query in Access

  • Thread starter Thread starter Kelii
  • Start date Start date
K

Kelii

.... if so, do you mind providing sample code?

I looked everywhere for this and can only find sample code for DAO.
I'm a big fan of DAO (who says ADO is simpler?) but I'm trying to get
into the 21st century.

This is what I have so far:
'Setup ADO for executing action query
Dim cmd As New ADODB.Command
cmd.ActiveConnection = gsCONNECTION
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qryRecipCompRevenuesAndDisputes_Step3"

'Execute
cmd.Execute

Needless to say it fails miserably and I'm embarassed to post it.

All help appreciated.

Thanks,

Kelii
 
On 11/07/2010 03:16, Kelii wrote:

Kelii,

Have you looked at http://support.microsoft.com/kb/278973


There is a downloadable file ExcelADO.exe

The text contains the following:

"7. Click Sample 4. This sample produces the same results as Sample 1,
but the technique that is used to transfer the data is slightly
different. In Sample 1, records (or rows) are added to the worksheet one
at a time. Sample 4 adds the records in bulk by attaching the Excel
table to an Access database and running an append query (or INSERT
INTO..SELECT FROM) to append records from a table in the Access table to
the Excel table. Once the transfer is complete, the Excel table is
detached from the Access database and the workbook that results is
displayed in Excel. Quit Excel, and return to the Visual Basic application."
 
Back
Top