SQL delete in EXCEL VBA

Question

I manage an Excel 2007 sheet using SQL, using the following connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source='Invoice.xlsm';Extended Properties='Excel 12.0;IMEX=1;HDR=Yes'

Everything works just as it should apart from the Delete statement. When I try to perform Delete From [tablename$] where code like '1'), I get an error -2147467259 (80004005).

I can't explain it, since all the other select and insert statements work just fine.

Ok. What ever I have tried, does not resolve the problem. I guess that perhaps Delete statements are not allowed for some reason. I am thinking of using a temporary sheet to write all data apart from the ones to be deleted and then ovewrite the original sheet with it. — tcop
What is the actual text of that error message? Does it offer any useful clues? — Jeff Rosenberg
I am afraid not. It is a general sql error reporting that data cannot be deleted — tcop

tcop tcop · Accepted Answer · 2013-02-12T15:43:02

After searching few hours in Microsoft Support, i found the following:

The Catalog object has a Create method but does not have a Delete method.

I guess there is no way to perform an SQL Delete statement, in excel sheets.

SQL delete in EXCEL VBA

1 Answers