VBA Access Function fncDeleteQuery Tutorial

Function deletes a query if it exists to use before creating a new query.



Purpose of Code


Microsoft Access returns an error message if the code tries to create a query with a name that already exists. This pair of functions checks to see if a specific query already exists and deletes it if it does.



Explanation


A name is chosen for the query and assigned to a variable (qName).

The variable qName is sent to the function fncDeleteQuery.

From there, qName travels to the function fExistQuery to see if a query with that name exists.

If the function fExistQuery finds a query with the same name, it's value is changed to true. Otherwise, it's value remains false.



Why two functions?


The user may not always wish to delete a query but may sometimes merely want to check whether a specific query exists. That is the reason for two functions:

  • the function fExistQuery returns True if the query exists and False if it does not exist
  • the function fncDeleteQuery deletes the query only if the function fExistQuery returns True


Code #1:


Function fncDeleteQuery(qName As String)
'deletes the query if fExistQuery returns true
If fExistQuery(qName) = True Then DoCmd.DeleteObject acQuery, qName
End Function



Code #2 fExistQuery


Function fExistQuery(qName As String) As Boolean
'Declare variables, db for the database and i as a number of the type integer to enumerate through the queries
Dim db As DAO.Database, i As Integer
'set db as the current database
Set db = CurrentDb
'start the boolean out as False and only change it if Visual Basic finds a matching query
fExistQuery = False
'refresh the database QueryDefs before accessing them
db.QueryDefs.Refresh
'loop through the database QueryDefs looking for the query that matches the'name of the query you passed to the function through the variable qName
For i = 0 To db.QueryDefs.Count - 1
     'compare lower case (LCase) versions of qName and the QueryDef names
     If LCase(qName) = LCase(db.QueryDefs(i).Name) Then
          'name matches so boolean is now true
          fExistQuery = True
          'name matches so exit loop 
          Exit For
     End If
Next i
 'done with db so set to nothing
Set db = Nothing
End Function




Requirements
      Microsoft Access 2002

Microsoft Access Required References
      Visual Basic for Applications
      Microsoft Access Object Library
      Microsoft DAO Object Library

To learn more about Microsoft Access references, see this post:
           
            Microsoft Access References
            http://www.vbquick.com/2011/09/microsoft-access-references_13.html


Do you like this post? Please link back to this article by copying one of the codes below.

URL:

HTML link code:

BB (forum) link code:

2 comments: