Link to home
Start Free TrialLog in
Avatar of Lia Nungaray
Lia NungarayFlag for United States of America

asked on

How to delete access queries using VBA

I have an amount of queries on an Access DB that I need to delete and recreate.  All of these queries start with "qry_get%". I have found several code snippets to check if the query exists and if it does, to delete it. Is there a way to do this with lesser code and in a cleaner fashion rather than create an if statement for each query? Thanks

Avatar of als315
als315
Flag of Russian Federation image

You can add line
On Error Resume Next

Open in new window

before starting delete and discard (or return) Error handling after finish:
On error goto 0

Open in new window

If you have no similar line in the beginning of sub and like to disable error handling
Or
On Error GoTo ErrorHandler

Open in new window

If you have error handling
In this case you will not get error if query is missing
Avatar of Norie
Norie

Can you post the code you have?
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi!

Here's an example code you can incorporate with error handlers and I have included notes in the code for your reference.

Option Compare Database
Option Explicit

Sub DeleteQueries()
    On Error GoTo ErrorHandler ' Turn on error handling
    
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim queryName As String
    
    Set db = CurrentDb() ' Set reference to current database
    
    ' Loop through all queries in the database
    For Each qdf In db.QueryDefs
        queryName = qdf.Name
        
        ' Check if query name starts with "qry_get"
        If Left(queryName, 7) = "qry_get" Then
            ' Attempt to delete the query
            On Error Resume Next ' Turn off error handling temporarily
            db.QueryDefs.Delete queryName
            On Error GoTo ErrorHandler ' Turn on error handling again
            
            ' Check if delete was successful
            If Err.Number <> 0 Then
                ' Check if error is due to existing relationships
                If Err.Number = 3734 Then
                    ' Get the name of the related table
                    Dim relTable As String
                    relTable = Mid(Err.Description, InStr(Err.Description, "'") + 1)
                    relTable = Left(relTable, InStr(relTable, "'") - 1)
                    
                    ' Display error message with related table name
                    MsgBox "Query '" & queryName & "' could not be deleted because it is related to table '" & relTable & "'. Please remove the relationship before deleting the query.", vbCritical, "Delete Query Error"
                Else
                    ' Display generic error message
                    MsgBox "Error deleting query '" & queryName & "': " & Err.Description, vbCritical, "Delete Query Error"
                End If
            Else
                ' Display success message
                MsgBox "Query '" & queryName & "' was successfully deleted.", vbInformation, "Delete Query"
            End If
            
            Err.Clear ' Clear any errors
        End If
    Next qdf
    
    Set db = Nothing ' Release reference to database
    
ExitSub:
    Exit Sub
    
ErrorHandler:
    ' Display error message
    MsgBox "Error deleting queries: " & Err.Description, vbCritical, "Delete Query Error"
    Resume ExitSub
End Sub

Open in new window


Hi Noah,

seems you have adapted this from a code to delete tables...
A query can always be deleted and has nothing to do with table relationships... ;)

For me Gustavs solution is the cleanest and shortest. (Although I would not use "Query" or "Name" for variable names.. ;)

Cheers,

Christian
@Bitsqueezer Yes, I agree! I just added those error handlers for my suggestion just in case, based on some unfortunate past experience when I tried deleting them using this alternative haha.

No worries. Cheers! :D