Invisible Query Head-Scratcher

[BillCo]

As part of my ongoing efforts to tidy up an old database by weeding out
old unused stuff, I exported all the query data by looping through
querydefs like so:

For Each qry In db.QueryDefs
'get qry.Name
'get qry.SQL
loop

This gave me the usual list of ~sq_* and user defined queries. Fine so
far.

I then proceeded to attempt to delete queries that I identified as
being unused, only to find that they were not there! Despite the fact
that I obtained the name and full SQL for these queries from the
QueryDefs they do not appear in the database window. Neither do they
make any appearance whatsoever in the MSysObjects table. They simply
dont exist!!

How are these "ghosts of queries long gone" in the database, and how do
I vanquish them?

I don't want to import objects into a new database, I'm more curious to
get to the bottom of this! And the first person to say compile or
decompile will get an e-slap (neither gets rid of them)!

[Tom van Stiphout]

On 8 Sep 2005 06:02:10 -0700, "BillCo" <co**********@gmail.com> wrote:

Why do you think they are invisible and hard to handle?
Right.
So leave them alone.

-Tom.

As part of my ongoing efforts to tidy up an old database by weeding out
old unused stuff, I exported all the query data by looping through
querydefs like so:

For Each qry In db.QueryDefs
'get qry.Name
'get qry.SQL
loop

This gave me the usual list of ~sq_* and user defined queries. Fine so
far.

I then proceeded to attempt to delete queries that I identified as
being unused, only to find that they were not there! Despite the fact
that I obtained the name and full SQL for these queries from the
QueryDefs they do not appear in the database window. Neither do they
make any appearance whatsoever in the MSysObjects table. They simply
dont exist!!

How are these "ghosts of queries long gone" in the database, and how do
I vanquish them?

I don't want to import objects into a new database, I'm more curious to
get to the bottom of this! And the first person to say compile or
decompile will get an e-slap (neither gets rid of them)!

[BillCo]

Thanks Tom, very helpful!

[BillCo]

They are invisible because they don't show up in the database window or
in the MSysObjects window. I never said they were "hard to handle".

The reason I don't want to leave them alone is that
1. They are there, and yet not there... wouldnt you be even a little
curious?
2. I've declaired war on bloat
3. I want to learn all about access, which means finding out where
these little blighters escaped the deletion net and how to finish the
job of removing them.

Anyone have any ideas?

[Tom van Stiphout]

On 8 Sep 2005 06:22:04 -0700, "BillCo" <co**********@gmail.com> wrote:

You tied one hand on your back. One of the VERY few options to remove
them you don't want to hear about.

-Tom.

They are invisible because they don't show up in the database window or
in the MSysObjects window. I never said they were "hard to handle".

The reason I don't want to leave them alone is that
1. They are there, and yet not there... wouldnt you be even a little
curious?
2. I've declaired war on bloat
3. I want to learn all about access, which means finding out where
these little blighters escaped the deletion net and how to finish the
job of removing them.

Anyone have any ideas?

[Tim Marshall]

BillCo wrote:

I then proceeded to attempt to delete queries that I identified as
being unused, only to find that they were not there! Despite the fact
that I obtained the name and full SQL for these queries from the
QueryDefs they do not appear in the database window. Neither do they
make any appearance whatsoever in the MSysObjects table. They simply
dont exist!!

How are these "ghosts of queries long gone" in the database, and how do
I vanquish them?

I don't want to import objects into a new database, I'm more curious to
get to the bottom of this! And the first person to say compile or
decompile will get an e-slap (neither gets rid of them)!

Have you tried turning your options to view hidden objects? <Now, don't
hit me!>

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[BillCo]

lol, I guess so!

They're gone now anyway... wherever they came from. Very strange, there
were 6 pages worth of query names, most of which were removed from the
database literally years ago - complete with spelling errors. I dont
know what brought them kinda half back to life ( I wonder if it had
anything to do with the decompile I ran a few days ago....), or why a
compact got rid of them now, when I compact this test db three or four
times a day anyway? I'll have to see if I can re-create this.

So in the mean time I guess the e-slap goes to me - *twack*!

[BillCo]

Yeah, I tried hidden objects, MSys tables etc. The only place they
existed was in the QueryDefs...

I don't want to import objects into a new database, I'm more curious to
get to the bottom of this! And the first person to say compile or
decompile will get an e-slap (neither gets rid of them)!

Let me re-phrase that, <i> neither GOT rid of them in the past!</i> but
today out of the blue... I dunno - if i didnt still have the word
document with lists of long dead queries I'd be questioning my sanity.

[polite person]

On 8 Sep 2005 06:02:10 -0700, "BillCo" <co**********@gmail.com> wrote:

As part of my ongoing efforts to tidy up an old database by weeding out
old unused stuff, I exported all the query data by looping through
querydefs like so:

For Each qry In db.QueryDefs
'get qry.Name
'get qry.SQL
loop

This gave me the usual list of ~sq_* and user defined queries. Fine so
far.

I then proceeded to attempt to delete queries that I identified as
being unused, only to find that they were not there! Despite the fact
that I obtained the name and full SQL for these queries from the
QueryDefs they do not appear in the database window. Neither do they
make any appearance whatsoever in the MSysObjects table. They simply
dont exist!!

How are these "ghosts of queries long gone" in the database, and how do
I vanquish them?

I don't want to import objects into a new database, I'm more curious to
get to the bottom of this! And the first person to say compile or
decompile will get an e-slap (neither gets rid of them)!

Hi
~ objects are things that have been deleted. Compact will get rid of them.

There is often a lot of crud in an old database, best removed by importing everything
into a new one though this needs care if the db is secured.

[Tim Marshall]

BillCo wrote:

Let me re-phrase that, <i> neither GOT rid of them in the past!</i> but
today out of the blue... I dunno - if i didnt still have the word
document with lists of long dead queries I'd be questioning my sanity.

Let's open an X-File on it... (note smily) 8) 8)

Seriously, I seem to recall seeing posts over the years here on similar
issues, ghosts of long dead objects and so on. Don't think I've
encountered it myself, but I'm still waiting for the aliens to swoop
down and show me clones of Jesus and Mohammed...

8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Phil Latio]

Am I right that if you have say a form, with a combo-box that has query
as a recordsource, wouldn't that show up as a querydef?
I did something similar to you and I had a load of these and if I
recollect, they were asscoiated with combo-boxes I had on a form!

I could be wrong, others may want to chime in and verify this though!

[Bob]

Try this in a new module:
'*********** code **********

Public Sub QFix()
Dim db As Database

Set db = CurrentDb
db.QueryDefs.Refresh
Set db = Nothing
Debug.Print "All done"

End Sub
'*********** end code **********
Call from debug window with
QFix

Bob

[BillCo]

indeed they do - they show up with names such as ~sq_frmMyForm_Combo1

The case here was different though, the queries were old user-saved
queries - that for some reason chose to re-appear in the querydefs and
vanished again on compact

[BillCo]

Thanks Bob, that's handy to know!

[Salad]

BillCo wrote:

indeed they do - they show up with names such as ~sq_frmMyForm_Combo1

The case here was different though, the queries were old user-saved
queries - that for some reason chose to re-appear in the querydefs and
vanished again on compact

I think the ~queries are compiled queries in Access...hidden because
they are system files. A compact should get rid of them if deleting the
one you view in the database window doesn't get rid of it. Ex:
Test
~test
Delete Test,, ~test is removed.

When looping throught queries you might save yourself grief by entering
If left(qdf.Name,1) <> "~" then
and when looping through tables
If left(tdf.Name,4) <> "Msys" then