Running delete query from Excel

[Guest]

Hi there,

From Excel I use ADO to export the Excel data to an Access table.
Before the new data may be imported, this Access table needs to be cleared,

To accomplish this I have created a delete query that deletes all records
from the table.
Currently I have to run this query manually before executing the ADO from
Excel.

Is it possible to run the delete query from Excel using VBA?

 

[Graham Mandeno]

Hi Charlie

I assume you have already opened a Connection object on your database file.

You can just use the Execute method of the Connection:

MyConnection.Execute "Delete * from [MyTable];"

 

[RoyVidar]

Hi there,

From Excel I use ADO to export the Excel data to an Access table.
Before the new data may be imported, this Access table needs to be
cleared,

To accomplish this I have created a delete query that deletes all
records from the table.
Currently I have to run this query manually before executing the ADO
from Excel.

Is it possible to run the delete query from Excel using VBA?

If you have an open connection cn, and your stored query is named for
instance qryMyDelete, you can execute it like this

cn.qryMyDelete

Else you could just fire off something like

cn.execute "DELETE FROM myTable", , adCmdText + adExecuteNoRecords