Solved

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

Posted on 2010-09-01
6
1,017 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
ID: 33580545
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
ID: 33580607
Thanks calacuccia, I had a suspicion this was true. Wonder if good old fashioned DAO would work?
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33580719
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:OutOnALimbAlways
ID: 33580929
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
ID: 33582259
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
ID: 33596970
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 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