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. — grecotron
– grecotron, Commented Feb 12, 2013 at 13:43
What is the actual text of that error message? Does it offer any useful clues? — Jeff Rosenberg
– Jeff Rosenberg, Commented Feb 12, 2013 at 13:59
I am afraid not. It is a general sql error reporting that data cannot be deleted — grecotron
– grecotron, Commented Feb 12, 2013 at 14:03

grecotron · Accepted Answer · 2013-02-12 15:43:02Z

2

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.

answered Feb 12, 2013 at 15:43

grecotron

3811 gold badge2 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rno Over a year ago

The ACE.OLEDB driver does not support Delete as you have found, but you can use another driver.

Collectives™ on Stack Overflow

SQL delete in EXCEL VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related