Solved

Delete query (vba) does not work for me in excel 2003

Posted on 2010-09-01
6
1,011 Views
Last Modified: 2013-02-22
Hello, I created a simple delete query and can't get it to work in excel 2003. The spreadsheet is just 1 column, A1 simply says name. Below is the code. The update query and recordset open work.
*******************************************************************************************************
Public Sub Test()
        'Spreadsheet has 'Name' written in cell A1. A2:A5 has any
        'data in it initially.
        'reference is microsoft active x data objects 2.8 library
        'mdado15.dll. vb version is 6.5. Excel version is
        '11.8302.8221 sp3 (excel 2003)
    Dim objConnection As New ADODB.Connection
    Dim objrecordset As New ADODB.Recordset
    Dim strSql As String
    Dim connstring As String
   
    Set objConnection = CreateObject("ADODB.Connection")
    Set objrecordset = CreateObject("ADODB.Recordset")

   objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"
            'simple update query works
    strSql = "UPDATE [Sheet1$] set [Name] = 'B'"
            'delete query does not work, but does not error out
            'when = is changed to <>.
            'Error 80004005
    ''strSql = "DELETE * from [Sheet1$] where [Name] = 'B'"
    objConnection.Execute strSql
    strSql = "Select * from [Sheet1$]"
            'works, displays 'B'
    objrecordset.Open strSql, objConnection
    MsgBox objrecordset!Name
End Sub

0
Comment
Question by:OutOnALimbAlways
  • 3
  • 2
6 Comments
 
LVL 17

Accepted Solution

by:
calacuccia earned 125 total points
Comment Utility
This article states:

Delete Records
Although the Jet OLE DB Provider allows you to insert and update records in an Excel workbook, it does not allow DELETE operations. If you try to perform a DELETE operation on one or more records, you receive the following error message:
Deleting data in a linked table is not supported by this ISAM.

http://support.microsoft.com/default.aspx?scid=kb;en-us;316934&Product=vbNET
0
 

Author Comment

by:OutOnALimbAlways
Comment Utility
Thanks calacuccia, I had a suspicion this was true. Wonder if good old fashioned DAO would work?
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
This is not my cup of tea, so you might wanna wait for a real DB specialist but from what I read about the subject, it appears to me that the only way to do this, is to link the Excel DB to an Access DB and delete the records there .... or use generic Excel VBA to do what you want.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:OutOnALimbAlways
Comment Utility
Yeah, I just remembered that DAO uses Jet too. I just got done testing it and the same thing happened. An update query worked, but the delete query failed miserably! I've been deleting records using a loop that starts from the bottom, (Step -1) but it takes about a million years, so I was looking for a better way.
0
 
LVL 16

Assisted Solution

by:Jerry Paladino
Jerry Paladino earned 125 total points
Comment Utility
I can verify that Excel records cannot be deleted with ADO, OLEDB or with ODBC via MS-Query.   Calacuccia - you mentioned linking the Excel file to an Access database and and delete the records from there.  That is also not an option since Linked Tables in Access cannot be modified.  I believe good old VBA is the path to take.
Instead of looping through the records one by one, perhaps you can use the AutoFilter in VBA to hide the records you want to keep then mass delete the remaining records in the worksheet.  Then unhide the filtered records.  I think this will be faster than looping through records one by one.
0
 

Author Closing Comment

by:OutOnALimbAlways
Comment Utility
Thanks--saved me some time. I'll probably be asking many more excel vba questions, just landed a job, but I'm pretty new to it!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now