Problem with QueryDefs in VBA

  • Thread starter Thread starter Rob Parker
  • Start date Start date
Another thing to do and this will be a pain but open every query (and
maybe unused forms, if there are any) and make sure that query isn't
hanging around somewhere. I know you are sure but it must be lurking
somewhere.
Click to expand...

And a more thorough method in this regard is to paste the following code
into a standard module and run it. The queries listed beginning with ~sq are
hidden queries attached to various objects like form controls.

Public Sub ListQdfs()
Dim qdf As DAO.QueryDef

For Each qdf In CurrentDb.QueryDefs
Debug.Print qdf.Name
Next
End Sub

My guess is that you'll turn up something in one of the ~sq items. When you
spot something suspicious, type this in the immediate window:

?CurrentDb.QueryDefs("QueryName").SQL

(replace the obvious)
 
Hi Stuart,

If you've been following this thread from the start, you would have seen
that what you are suggesting runs fine. It gives me a list of all the
queries (including the system/hidden ones, starting with ~). The problem is
that when I use the qry.Parameters.Count property - to list only those
queries which have a parameter - it falls in a heap, and says it can't find
a query which doesn't exist - and which does not appear in the list of
queries produced by the code you offered.

Rob
 
Hi Gina,

I think your second suggestion here has nailed it. There is another query
in the db (one which I am almost certain is no longer used anywhere) which
uses the tblWA_ct query that no longer exists.

It seems that code which simply loops through the QueryDefs collection for
the names of queries does nothing else behind the scenes; but when it needs
to check each query's parameters it needs to open the query, and that's when
it fails.

Problem solved.

Thanks,

Rob

PS. Fortunately, the offending query was near the top of the list of about
300, so it wasn't too much of a pain ;-)
 
Back
Top