Solved

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

Posted on 2010-09-01
6
1,026 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

630 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