Ms-access – Where can a query be found in a Microsoft Access database besides “Queries”

ms-access

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?

Best Answer

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.

Related Topic