Trying to Delete queries

[Duck]

I am trying to delete all the old queries from my database from the
switchboard when a certain button is pressed after a certain date.
This is to disable the database if payment for it's use is not
received. I placed the following code in the on_click event of one of
the buttons:

If (Now() > #6/13/2007#) Then
Dim MedDB As DAO.Database
Dim qdf As QueryDef
Set MedDB = CurrentDb
For Each qdf In MedDB.QueryDefs
DoCmd.DeleteObject acQuery, qdf.Name
Debug.Print qdf.Name & " Deleted"
Next qdf
Else
Debug.Print "It is not time yet"
End If
I get an error message saying that: "There was an error executing the
command", and in the immediate window I see:

~sq_drptWeeklyMedsByProgramThisWeek~sq_dProgram Deleted
~sq_drptWeeklyPRNByLast4~sq_dProgram Deleted
~sq_drptWeeklyPRNByProgram~sq_dProgram Deleted
~sq_drptWeeklyPRNNextWeek~sq_dProgram Deleted
~sq_drptWeeklyPRNThisWeek~sq_dProgram Deleted
~sq_dtestrptWeeklyMedsAllThisWeek~sq_dProgram Deleted

However none of the queries seems to have been deleted...what am I
doing wrong? or better yet is there an easier way to totally disable
the database after a certain date for everyone except the owner of the
database and it's tables?

 

[Allen Browne]

When you use a query statement in the RecordSource of a form/report or the
RowSource of a combo/list box, Access creates a hidden query to manage it.
That's what these "~sq_*" queries are.

If you want to delete all queries, try looping backwards through the
QueryDefs collection. It avoids the problem with the queries disappearing
from the For Each loop as you kill them.

This kind of thing:

Function KillAllQueries()
Dim db As DAO.Database
Dim i As Long

If MsgBox("Delete ALL queries in this database?", _
vbYesNo + vbDefaultButton2 + vbExclamation, _
"WARNING!") = vbYes Then
Set db = CurrentDb()
For i = db.QueryDefs.Count - 1 To 0 Step -1
db.QueryDefs.Delete db.QueryDefs(i).Name
Next
Set db = Nothing
End If
End Function