CurrentDb.QueryDefs returning obsolete definitions?

[rich.barry]

Hello

I have a bit of code (below) that writes my QueryDefs to a table so that after any changes to object names, I can search the querydefs to check if there are any places referencing the old object name that I need to update.

For C = 0 To CurrentDb.QueryDefs.Count - 1
            With Record
                .AddNew
                !QueryName = CurrentDb.QueryDefs(C).Name
                !Querysql = CurrentDb.QueryDefs(C).SQL
                .Update
            End With
Next C

I seem however to get a number of querydefs returned that refer to objects that no longer exist as far as I can see.
e.g. ~sq_cswbCreatePack~sq_cListBilletStock
swbCreatePack was a form that existed a long time ago, but is no longer listed amongst the forms.

Can anyone shed any light on why I get these querydefs that shouldn't exist anymore?

Cheers

Richard

 

[jdraw]

The ~names may be deleted "things", or may even (in some cases) be objects that access created/destroyed for some reason.

Have you done a compact repair on your database? I think many of these items would be removed (garbage collection and space recovery) by doing a compact and repair.

Good luck. Let us know what you find.

I have often seen procedures such as yours that have a

If  !ObjName Like "~" Or  _
      Left(!ObjName ,4) Like "MSys" Then
  'skip that obj
else
    .addnew
  .....

end if

to skip over "~" (temporary/deleted "things") or MSys [system] things

 

[rich.barry]

Unfortunately compact and repair doesn't clean up this rather persistant rubbish.
The ~ items include the form listboxes, combo boxes etc with SQL for the RowSource, so most of them are quite relevant.

It appears that to get rid of the ones that shouldn't be present, I have to re-create the form and control then delete it. Well at least I have found a way of tidying it up now.

 

[jdraw]

I recall reading somewhere that there are temporary queries/forms whatever created in some processes that are used by Access. I can't find the reference at the moment, but that's what I was basing my comment on.

You say

It appears that to get rid of the ones that shouldn't be present,

but I don't quite understand this last part

I have to re-create the form and control then delete it.

If C&R gets rid of what if should, what is it that you have to recreate?

 

[rich.barry]

C&R did not fix anything.

What I had were a number of query defs for controls on forms that had been deleted a long time ago. C&R had been carried out multiple times since the form had been deleted, but the query defs that weren't linked to anything still existed.

To get rid of the query def, I looked at the name or the control and form in the query def, created a new form and control with those names, which made the database update the query def, then deleted the form, which deleted both the form and the query def.
Why it missed deleting the query def when the form was originally deleted is unknown.

 

[Pat Hartman]

These querydefs are the result of SQL strings embedded in RecordSource or RowSource properties. Access saves them with names that sort of reflect where they are used.

 

[jdraw]

Thanks Pat.