Number of querydefs in my database !?

[Laban Twissel]

Hi All !

I'm puzzled with th number of querydefs I obtain when using the count
method of currentdb.querydefs object, which is much higher than the
recorded queries !
It appears that all the querydefs that I've created and suppressed during
developpement of my database stay somewhere in the database ! I managed to
put their names and SQL property in some table. All those already
suppressed queries have strange names with ~ symbols and fragments of
understandable names.

Does anyone know how I could suppress all those queries (around a hundred
!) since they take place and fake the statistics of my database (declaring
140 recorded queries instead of 40...)

I hope anyone could dissolve the mistery

 

['69 Camaro]

Hi, Laban.

It appears that all the querydefs that I've created and suppressed during
developpement of my database stay somewhere in the database !

I'm not quite sure what you are referring to by the term "suppressed during
developpement," other than possibly "created, modified, then deleted" from
the list of queries that show up in the database window.

All those already
suppressed queries have strange names with ~ symbols and fragments of
understandable names.

The Jet engine needs to keep track of these queries, so they're listed in
the MSysObjects table. Do you see names like ~sq_ffrmOrderDetails? That
would be the record source for the frmOrderDetails form. There can be other
QueryDefs that Jet needs to keep track of for record sources or SQL
statements used throughout the database for objects you've created, so there
may be more than one of these tilde-prefixed names per form or report in the
database.

since they take place and fake the statistics of my database (declaring
140 recorded queries instead of 40...)

When checking for queries displayed only in the database window, just count
the names that don't have the ~ or ~sq_f prefix.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[George Nicholson]

Have you compacted recently?

 

['69 Camaro]

Hi, George.

Have you compacted recently?

Believe it or not, one _never_ needs to compact the database just to get rid
of any deleted QueryDefs from the QueryDefs Collection count. (Compaction
will remove the deleted QueryDefs, reclaim the file space, and adjust the
QueryDefs Collection count of course, but it's not required in order to
merely reduce the QueryDefs Collection count after deleting the queries.)

The only steps necessary to get an accurate count after deletion of the
QueryDefs are to close, and then reopen the database. (Refreshing the
QueryDefs Collection isn't enough to get an accurate count after deleting
queries.) Those queries Jet has marked for deletion will be gone from the
collection the next time the database is opened, and the QueryDefs
Collection count will once again be accurate.

It doesn't appear that Laban was describing deleted queries, though. A
query marked for deletion will be temporarily renamed to ~TMPCLP, followed
by several digits assigned by Jet (which is five or six digits whenever I've
looked at these). This naming convention doesn't quite match Laban's
description of the names, which he describes as strange names with tildes
and "fragments of understandable names":

Jet's internal QueryDefs are named something like ~sq_ffrmOrderDetails,
which might indicate the record source for the frmOrderDetails form.
(That's certainly a strange name with a tilde and a fragment of an
understandable name, if ever I saw one.)

If a count of the QueryDefs Collection returns 140 immediately after opening
the database, then there really are 140 QueryDefs, even though Laban only
defined 40 of them -- the ones displayed in the database window. The rest
are QueryDefs that Jet is maintaining internally as I described in my
previous post.

Iterating through the QueryDefs Collection and only counting the QueryDefs
that don't have a tilde prefix will reveal the number of normal user-defined
QueryDefs that Laban is actually interested in.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Laban Twissel]

Thanks a lot for answering my question

In fact, I thought that some of the queries appearing with the querydefs
object were supressed ones because there "strange" names contained names of
forms that doesn't exist any more. I think now that these are original
names of forms still existing, but which names I have changed.

Cheers,

Laban.