Deleting Querydefs

[johnnymagee]

I create a number of queries prefixed "qryTemp", i.e. "qryTemp0", "qryTemp1", "qryTemp2", etc

Previous incarnations of these queries have to be closed and deleted before making new ones. This code (sort of) works but only finds up every second relevant query. I can't see what the problem is...

const sQdfName as string = "qryTemp"
dim qdf as dao.querydef

for each qdf in currentdb.querydefs
if left(qdf.name,len(sqdfname))=sqdfname then
docmd.close acquery, qdf.name
currentdb.querydefs.delete qdf.name
end if
next

 

[tonyflavell]

Hi there

It would seem that when you delete the querydef the pointer must move to the next available querydef and then the "for..next" statement moves the pointer again. Try it without the "close" statement, as follows:-

const sQdfName as string = "qryTemp"
dim qdf as dao.querydef

for each qdf in currentdb.querydefs
  if left(qdf.name,len(sqdfname))=sqdfname then
    currentdb.querydefs.delete qdf.name
  end if
next

Just an idea

Regards

Tony

 

[johnnymagee]

Many Thanks. I do need to close the queries first as they are displayed but the pointer comment pointed me in the right direction (hoho). This seems to work...

With CurrentDb
lNumQueryDefs = .QueryDefs.Count
lQuery = 0
lDeletedQueries = 0
Do Until lNumQueryDefs = lQuery + lDeletedQueries
If Left(.QueryDefs(lQuery).Name, Len(sQdfName)) = sQdfName Then
DoCmd.Close acQuery, .QueryDefs(lQuery).Name
.QueryDefs.Delete .QueryDefs(lQuery).Name
lDeletedQueries = lDeletedQueries + 1
lQuery = lQuery - 1
End If
lQuery = lQuery + 1
Loop
End With

 

[PHV]

A simpler way:
With CurrentDB
For lQuery = .QueryDefs.Count -1 To 0 Step -1
If .QueryDefs(lQuery).Name Like sQdfName & "*" Then
DoCmd.Close acQuery, .QueryDefs(lQuery).Name
.QueryDefs.Delete .QueryDefs(lQuery).Name
End If
Next
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244