Solved Another query query

[John Sh]

I am running basic code to output the contents of querydefs to a file and keep getting a query that does not appear in the queries pane, nor is it used in any procedure.
I have run a compact and repair but am still seeing this query in any list created immediately after.
My question is, at what point is querydefs updated.

I have named "Redundant" queries with a preceding "2" such that if there are no errors regarding said query it can be safely deleted or restored if needs be, hence the second part of the if statement.

Set rs = oDB.OpenRecordset("Query_Temp", dbOpenDynaset)
        For Each qdf In db.QueryDefs
            If InStr(qdf.Name, "~") = 0 And InStr(qdf.Name, "2") = 0 Then
                rs.AddNew
                    rs!query_name = qdf.Name
                    rs!query_text = qdf.SQL
                rs.Update
            End If
        Next qdf

Here is a screen shot of three tables with "QBBoxcollectH" showing and a query search window with "qbb" as the search criteria.

 

[The_Doc_Man]

My question is, at what point is querydefs updated.

When doing anything to infrastructure, ALWAYS have warnings enabled. It provides important feedback that something is about to be changed, which is a relevant part of your "when" question.

Usually if you create or update a query, QueryDefs gets updated when you hit the SAVE icon that looks like a little diskette. When you delete a query, QueryDefs gets updated when you confirm the deletion.

Sometimes when I've been delving into infrastructure and finish one modification, I hit the SAVE icon just for snorts and giggles. In later versions of Office stuff, that icon will sometimes "grey out" if there is nothing to be saved, but older versions did that differently so there it is harder to be sure.

The only time that I would worry about QueryDefs updates NOT being saved is if you exited from Access in a non-standard way including power loss, using Windows Task Manager to terminate the task, or from the CMD prompt issuing a KILL for the specific task number. A normal "QUIT" should save anything pending, and a VBA Application.Quit should do the same thing.

 

[John Sh]

When doing anything to infrastructure, ALWAYS have warnings enabled. It provides important feedback that something is about to be changed, which is a relevant part of your "when" question.

That's all very good, but why is that query showing in querydefs and not in the query pane?????

As for warnings, they are normally on except when I am removing data from a table or other times when I don't want the users to be alarmed by a message that will ultimately be ignored.

 

[Galaxiom]

I have named "Redundant" queries with a preceding "2"

Do you have NameAutoCorrect turned off?

 

[CJ_London]

That's all very good, but why is that query showing in querydefs and not in the query pane?????

Perhaps you have set the hidden property to true?

perhaps you are using nav pane grouping of objects so it appears somewhere else?

 

[John Sh]

Do you have NameAutoCorrect turned off?

No idea. Where do I find it and what relevance does it have to renaming queries etc.

 

[Galaxiom]

As for warnings, they are normally on except when I am removing data from a table or other times when I don't want the users to be alarmed by a message that will ultimately be ignored.

Use CurrentDb.Execute with dbFailOnError. It doesn't produce warnings but won't ignore errors.

No idea. Where do I find it and what relevance does it have to renaming queries etc.

If it is on, which it is by default, renaming objects will propagate to other objects that use them.

Set name AutoCorrect options - Microsoft Support

Avoid having your Access desktop database break everytime there is a design changes by using the Name AutoCorrect feature. The Name AutoCorrect can detect and automatically correct problems.

support.microsoft.com

 

[John Sh]

Use CurrentDb.Execute with dbFailOnError. It doesn't produce warnings but won't ignore errors.


If it is on, which it is by default, renaming objects will propagate to other objects that use them.

Set name AutoCorrect options - Microsoft Support

Avoid having your Access desktop database break everytime there is a design changes by using the Name AutoCorrect feature. The Name AutoCorrect can detect and automatically correct problems.

support.microsoft.com

It is off. I have recently changed some table names and had to correct the queries manually but a check in options revealed it is not active.

 

[John Sh]

It was hidden. Don't know how, as I never hide objects.
I guess it's just Access being consistently inconsistent.
Thank you to those who helped.

 

[CJ_London]

I guess it's just Access being consistently inconsistent.

Don't blame access, much more likely you hid it without realising it - the hide option is right below the rename option and you clicked it in error

 

[Papa_Bear1]

Don't blame access, much more likely you hid it without realising it - the hide option is right below the rename option and you clicked it in error

Just a little tidbit.
I normally hide almost everything - so that the casual user won't see things they shouldn't be messing with, but - I always have my Access system set up to show me hidden objects. They show up sort-of half-way grayed out, but they show up. This would have prevented this situation for you if you had it set to show hidden objects (in Options).