Solved

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

Posted on 2010-09-01
6
1,019 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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