Queries:Does Query Exist?

Sometimes it's handy within Visual Basic to check & see if a query exists before performing certain actions. In my case, I needed this because I was exporting a report to Excel format by using a temporary query created from SQL statements and then the query needed to be deleted. If a programming error occurred--which could happen simply by the user cancelling the exporting action--the temporary query remained, resulting in a programming error the next time the code ran again (because it would try & create a query that already existed). It thus became necessary for me to check & see if this query existed before creating it, and if it did, deleting it before proceeding.

Anyway, to perform this action:

1. Create a new module.
2. Enter the following code into the module:

   
   Public Function DoesQueryExist(strName As String) As Boolean
       Dim loDb As Database
       Dim loQdf As QueryDef
       On Error Resume Next
       Set loDb = CurrentDb
       Set loQdf = loDb.QueryDefs(strName)
       DoesQueryExist = Not CBool(Err)
       Err.Clear
   End Function
   
   

3. Save and close the module. Name it something like basDoesQueryExist
4. The code which would check for the query would look like this:

   
   If DoesQueryExist("nameofquery") = True Then
   ' Code to execute if query DOES exist 
   Else
   ' Code to execute if query does NOT exist
   End If
   

   
   
   Webpage created using Microsoft WordPad running on Windows 2000.