Where can a query be found in a Microsoft Access database besides "Queries"?

Question

So I have learned a bit about databases and Microsoft Access specifically, trying to help maintain a Microsoft Office Access 2003 database with Visual Basic scripting (version 6.5) which was set up by several people who are no longer with the company. Now we have the problem that the data we're getting from an outside source can no longer be imported by the database scripts; something about the format has changed but we can't figure out what.

I'm trying to dig into the scripting to figure out what's changed and what might be the optimal solution for getting things working again. We've narrowed down the problem somewhat; it's in the last line in this code snippet:

Debug.Print "step #255"
DoCmd.OpenQuery "qryMAKE NEW GHR TABLE", acNormal, acEdit

Debug.Print "step #256"
DoCmd.OpenQuery "qryUPDATE MIDDLE INI", acNormal, acEdit

Debug.Print "step #257"
DoCmd.OpenQuery "qryUPDATE SUFFIX", acNormal, acEdit

Now, here's the part that has me completely confused! When I open up the database, I see the dialog that gives me an option of looking at "Tables", "Queries", "Reports", "Forms", etc. I would have assumed that all queries in a database would be findable under the "Queries" section. But of the three queries that are called in the above code snippet, only qryMAKE NEW GHR TABLE is actually there. The other two certainly act as if they exist - the debug output prints "step #256", "step #257" and doesn't complain in between about not being able to find qryUPDATE MIDDLE INI - but I cannot find this query or qryUPDATE SUFFIX.

The only thing I've been able to figure about what might be happening is that Microsoft's documentation says that it looks for queries in the database itself but might also look in a "library database". Unfortunately, I can't seem to find any information on what "library databases" are, certainly not enough to figure out if we're using one; trying to search on "library database" only brings up results about databases being used in and by lending libraries.

Where do we need to look for qryUPDATE MIDDLE INI and qryUPDATE SUFFIX?

Answer 1

Maybe somebody hid them... Objects in an Access database can be marked as hidden. If you right click and look at Properties you'll see a hidden flag. To unhide I think you go into Tools | Options | View and there is a checkbox saying show hidden objects.

Hope that's it, because I can't think of anything else.

Answer 2

When you look at references (Tools-references from a code window), can you see a reference to an Access mde or mdb? Does the code create the query and then delete the query, you can search for the two queries by name in the code, and see what it turns up.

Answer 3

If the query is in the current database, you should be able to see it in the QueryDefs collection. Hit Ctrl-G to go to the Immediate Window in the VBE and type this;

  ?CurrentDB.QueryDefs("qryUPDATE MIDDLE INI").Name

If it returns qryUPDATE MIDDLE INI" then it's there, just not necessarily visible. To find out, type this:

  ?GetHiddenAttribute(acQuery,"qryUPDATE MIDDLE INI")

That will return True if it's been set to be hidden, an then you can change your Access options to display hidden objects.

Answer 4

Did you check for their existence while stepping thourgh the code? It can be that these queries are created and destroyed in the same function... They exist while the code is running, but you won't see them before and afterwards...