Problem with QueryDefs in VBA

[Gina Whipp]

Rob,

I have another thought...

ONLY do this on a BACK-UP COPY!!!

After making the BACK-UP, in the BACK-UP copy, uncheck all the References
that will allow you to do and then close the database. Open the database
and try running it again.

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.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Stuart McCall]

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.

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)

 

[Rob Parker]

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

 

[Rob Parker]

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 ;-)

 

[Gina Whipp]

Rob,

Glad we finely got that mystery cleared up (and glad it didn't take ALL
day)!

You're welcome...
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm