Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1059
  • Last Modified:

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

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
OutOnALimbAlways
Asked:
OutOnALimbAlways
  • 3
  • 2
2 Solutions
 
calacucciaCommented:
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
 
OutOnALimbAlwaysAuthor Commented:
Thanks calacuccia, I had a suspicion this was true. Wonder if good old fashioned DAO would work?
0
 
calacucciaCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
OutOnALimbAlwaysAuthor Commented:
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
 
Jerry PaladinoCommented:
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
 
OutOnALimbAlwaysAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now