CODEWITHSUNDEEP
ms-accessvba
Catinca
Catinca

Reputation: 126

Check if a query exists using VBA

I have a procedure in VBA which runs a report based on what user select on a dropdown (Report Name and grouping criteria) in a form. Also, I have a button which that user selection can be transferred to Excel. The part of the procedure where I want to test if the query exist is below:

If Not IsNull(Me.cmbGroup.Value) Or Me.cmbGroup.Value = "" Then

    strSQL = "SELECT * FROM qryCrossTotGroup WHERE [Group]='" & Me.cmbGroup.Value & "'"
    'MsgBox strSQL

    With MyDatabase
        .QueryDefs.Delete ("tmpOutQry")
        Set MyQueryDef = .CreateQueryDef("tmpOutQry", strSQL)
    End With

    'Step 3: Open the query
    Set MyRecordset = MyDatabase.OpenRecordset(strSQL)

    'Step 4: Clear previous contents
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = True
        .Workbooks.Add
        .Sheets("Sheet1").Select

    'Step 5: Copy the recordset to Excel
        .ActiveSheet.Range("A2").CopyFromRecordset MyRecordset

    'Step 6: Add column heading names to the spreadsheet
        For i = 1 To MyRecordset.Fields.Count
            xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
        Next i
        xlApp.Cells.EntireColumn.AutoFit
    End With

End If

I want to test if query "tmpOutQry" exist in order to delete it. Have someone that line of code?

Upvotes: 1

Views: 16975

Answers (3)

Harry White Dewulf
Harry White Dewulf

Reputation: 41

Here is the code I use for this; I don't like the idea of iterating through every QueryDef in a database, and sometimes with VBA you have to accept that not every collection or type has the methods that will enable you to test it "conventionally."

On Error Resume Next
    If CurrentDb.QueryDefs("queryDefName").Name = "queryDefName" Then
    'It exists; do something

    ElseIf Err.Number = 3265 Then
        Err.Clear
        'it doesn't exist; do something else
        
    End If
On Error GoTo 0

Using a precise error number (3265 is "item not in collection") is as close as you can get, AND you don't needlessly iterate over what in some Access applications can be a large collection.

Upvotes: 1

Catinca
Catinca

Reputation: 126

I tried this code and worked:

With MyDatabase
For Each MyQueryDef In CurrentDb.QueryDefs
 If MyQueryDef.Name = "tmpOutQry" Then

   .QueryDefs.Delete ("tmpOutQry")
   Exit For
   End If
   Next

    Set MyQueryDef = .CreateQueryDef("tmpOutQry", strSQL)
End With

Upvotes: 4

Gustav
Gustav

Reputation: 55831

You don't use the temp query, so you can reduce your first steps to:

strSQL = "SELECT * FROM qryCrossTotGroup WHERE [Group]='" & Me.cmbGroup.Value & "'"
'MsgBox strSQL

'Step 3: Open the query
Set MyRecordset = MyDatabase.OpenRecordset(strSQL)

Upvotes: 4

Related Questions