Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: Delete All Queries at the same time

    Hi i am trying to delete all my queries in one go, is there an easy way to do this using a macro, module please?

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I SURE HOPE YOU KNOW WHAT YOU ARE DOING.
    WHY WOULD YOU WANT TO DELETE ALL THE QUERIES?

    This is the first time that I know how to answer your request, but have choosen not to reply to your request because I need a REAL GOOD reason as to why you want to delete all the queries before I want to participate in helping to delete all the queries in an application.

  3. #3
    Join Date
    Mar 2003
    Posts
    225
    Hi i have changed a lot of my data but now have numerous duplicates, i have all my new queries in one database ready to import, once i have cleared out all the old ones....

    i know this may sound a bit daft, just want to get rid of all the old unsed data

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    GolferGuy, good question!

    I understand GolferGuy's trepidation in answering, because someone could use it to destroy someone else's database. Even if that was your intention, I can't imagine you telling us so. Sort of like asking someone if their a terrorist on a visa application...

    In any event, I will work under the assumption that you have a valid reason to ask for this. But even if that is the case, there's no stopping someone who wants to use this code maliciously from doing so.

    Here's one way to do it with code using DAO's Querydefs collection.
    Code:
    Public Sub DelAllQrydef()
    
        On Error GoTo ErrMsg:
    
        If MsgBox("Delete ALL queries?", vbYesNo + vbCritical) = vbYes Then
    
            Dim db As DAO.Database
            Dim qdefs As DAO.QueryDefs
            Dim i As Long, iMax As Long
    
            Set db = CurrentDb()
    
            iMax = db.QueryDefs.Count
    
            If iMax > 0 Then
                For i = 1 To iMax
                    Debug.Print db.QueryDefs(0).Name
                    db.QueryDefs.Delete (db.QueryDefs(0).Name)
                Next i
            End If
    
        End If
    
    
    ExitHere:
        On Error Resume Next
        db.Close
        Exit Sub
    
    ErrMsg:
        MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
        Resume ExitHere:
    End Sub

    THERE IS NO UNDOING THIS ONCE THE CODE IS RUN!
    Last edited by Cosmos75; 03-08-07 at 03:46.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    Mar 2003
    Posts
    225
    I understand your concerns, and all i can do is assure you i only have good intentions for the use of this code...

    It worked a treat and saves me tons of time hitting delete...

    could this code be modified to delete the tables too? not at the same time as the above code but a different admin option...?

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Yes, except I would have done in with a
    dim qrydef as dao.querydef
    For each qrydef in currentdb.querydefs
    docmd.delete acquery, qrydef.name
    Next qrydef

    To do it for tables, change the qrydef to tbldef, and make the tbldef a dao.tabledef in the dim statement.

    Actually this can be reversed if you backup your database first, which it sounds like you didn't do.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    EDIT - removed dumb post
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Surely if you're deleting all these tables and queries it would just me simpler to create a new db and import only the ones you want to keep?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •