Delete QueryDef? (1 Viewer)

[DAW]

Simple one this maybe! I use the following which works fine:

CurrentDb.QueryDefs.Delete "qryTemp"

...but only if "qryTemp" exists of course. I can't guarantee it always will so i'm looking for kind of

IfExists("qryTemp")

does such exist?

 

[redneckgeek]

You could loop through all your querydefs

...
If FindQuery("qryTemp") = True Then
CurrentDb.QueryDefs.Delete "qryTemp"
End If
 
 
Function FindQuery (strQueryName) as Boolean
For Each QueryDef in CurrentDB.Querydefs
If Querydef.Name = strQueryName Then
FindQuery=True
Exit Function
End if
Next
FindQuery=False
Exit Function
End Function

Or you could just cheat and

On Error Resume Next
CurrentDb.QueryDefs.Delete "qryTemp"

 

[DAW]

You could loop through all your querydefs
....

Wonderful - thanks a lot!

Or you could just cheat and

Tut tut! Naughty

 

[DAW]

Sorry to come back on this again but i'm getting a 'variable not defined' error on the line "For Each QueryDef in CurrentDB.Querydefs" pointing at "QueryDef". Why is this? I thiought as it is an object not a variable it shouldn't be defined...

You could loop through all your querydefs

Function FindQuery (strQueryName) as Boolean
For Each QueryDef in CurrentDB.Querydefs
If Querydef.Name = strQueryName Then
FindQuery=True
Exit Function
End if
Next
FindQuery=False
Exit Function
End Function

 

[DAW]

Apologies - its early morning!

Public Function FindQuery(QName) As Boolean
    Dim QryDef As QueryDef
    '----------
    For Each QryDef In CurrentDb.QueryDefs
        If LCase(QryDef.Name) = LCase(QName) Then
            FindQuery = True
            Exit For
        End If
    Next
End Function

 

[wazz]

you do need
Dim qdef As QueryDef
For Each qdef in CurrentDB.Querydefs

btw, you can use the same looping idea for modules, forms, tables, etc.

i'm still searching for a really good explanation as to when this is necessary and when it isn't. there's a thread around here about setting or not setting recordset variables that helps explain it somewhat but not completely. it seems to be a top-down thing. currentdb is what it says and doesn't need defining. the querydefs collection is automatically a part of that and doesn't need defining. but a single querydef in the collection needs defining because it could be one of many. (?)

any other tips about this?

 

[gemma-the-husky]

if its STILL failing, it might be the currentdb falling out of scope-

try this

Function FindQuery (strQueryName) as Boolean
dim dbs as database

set dbs=currentdb
For Each QueryDef in DBS.Querydefs
   If Querydef.Name = strQueryName Then
      FindQuery=True
      Exit Function
    End if
Next
FindQuery=False
Exit Function
End Function

HOWEVER - this method reads every query, to see if one exists - very inefficient - if you just want to see if one query exists, just try reading it directly - so instead

Function FindQuery (strQueryName) as Boolean
dim dbs as database
dim found as boolean
set dbs=currentdb

on error goto fail
'next line succeeeds without error if qry exists
found = len(dbs.querydefs(strqueryname).name)>0

exithere:
findquery = found
exit sub

fail:
'no query of that name
found=false
resume exithere

End Function

and I actually agree with the early point by redneckgeek
if you just want to delete the query then simply the following code
either deletes the query, or fails, because there is no query - either way you are OK

On Error Resume Next
CurrentDb.QueryDefs.Delete "qryTemp"