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.
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
Function fncDeleteQuery(qName As String)
'deletes the query if fExistQuery returns true
If fExistQuery(qName) = True Then DoCmd.DeleteObject acQuery, qName
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
'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
'done with db so set to nothing
Set db = Nothing
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
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: