Can you search the list of Queries in an Access Database?

[Guest]

I was wondering if it's possible to query the list of queries that show up in
an Access database. We have a lot of queries in our database and it would be
easy to check for the existence of a query by looking for something like
QryEmp*.

 

[Granny Spitz via AccessMonster.com]

I was wondering if it's possible to query the list of queries that show up in
an Access database. We have a lot of queries in our database and it would be
easy to check for the existence of a query by looking for something like
QryEmp*.

Turn on system objects, Tools | Options | View | Show System Objects. Open
the MSysObjects table and search or filter the Name column for whatever query
you want.

 

[Van T. Dinh]

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*")
AND ((MSysObjects.Type)=5));

should give you a list of all saved Queries in your database.

 

[Guest]

What does the ~ represent?

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*")
AND ((MSysObjects.Type)=5));

should give you a list of all saved Queries in your database.

 

[Van T. Dinh]

The SQL Strings we used RowSources for ComboBoxes, etc ... are actually
stored as (hidden) Query objects with name starting with "~". Since you
only want the Queries you actually saved, I included the criteria to remove
the "~" queries from the returned dataset.

 

[Pieter Wijnen]

you can always include a join to MSysQueries instead of the type=5 argument
;-)

Pieter

 

[Guest]

Thanks for this info. Where is the description stored?

 

[Van T. Dinh]

Description is a user-define Propery of the the QueryDef object. If you
want to retrieve the Description, you need to go through its Properties
collection, e.g.:

CurrentDB.Querydefs("QueryName").Properties("Description")

Note: This property may not exsit for every QueryDef object so in code you
need to trap and ignore the error when the Property Description doesn't
exist.

 

[James A. Fortune]

Turn on system objects, Tools | Options | View | Show System Objects. Open
the MSysObjects table and search or filter the Name column for whatever query
you want.

Why are the MSys tables, undocumented and subject to change between
versions, used to get this information? Isn't there a document object
in the containers collection that's supposed to be used to get this
information? Are they used because it's easier to use the MSys tables
directly in a query rather than to create a User-Defined Function (UDF)
in order to access the collection since the collection belongs to a
database object? Does the document object have other limitations or
bugs that the MSys tables do not? This looks to me like another case of
adopting bad practices for the sake of convenience. I don't need a list
of queries much so please excuse my ignorance if I'm missing something
obvious.

James A. Fortune
(e-mail address removed)

 

[dbahooker]

it depends.. if someone else is already querying the table; you might
have random locking problems

MDB is prone to crap like that
use a real platform; like Access Data Projects

 

[Granny Spitz via AccessMonster.com]

Why are the MSys tables, undocumented and subject to change between
versions, used to get this information?

The system tables are in Microsoft's KB articles, so if Microsoft writes
about them and posts it on the web I think they're officially documented.
(You may want *more* documentation, but this is what's been made available.)
And if they're subject to change, please name the version of Access which
doesn't have the Name column in the MSysObjects table where my suggestion
won't apply.

Isn't there a document object
in the containers collection that's supposed to be used to get this
information?

I don't think you can point and click your mouse and type in a single search
string to get the list of matching queries with a document object like you
can in a datasheet.

Are they used because it's easier to use the MSys tables
directly in a query rather than to create a User-Defined Function (UDF)
in order to access the collection since the collection belongs to a
database object?

Yes, it's easier to point and click than it is to write VBA code for most
people.

Does the document object have other limitations or
bugs that the MSys tables do not?

It doesn't open in datasheet view so you can sort and filter with your mouse
and the toolbar.

This looks to me like another case of
adopting bad practices for the sake of convenience.

People can't change, add or delete anything in the system table when they
open it in datasheet view. They're looking at read-only data. Why is that a
bad practice?

 

[James A. Fortune]

The system tables are in Microsoft's KB articles, so if Microsoft writes
about them and posts it on the web I think they're officially documented.
(You may want *more* documentation, but this is what's been made available.)
And if they're subject to change, please name the version of Access which
doesn't have the Name column in the MSysObjects table where my suggestion
won't apply.

I'm not saying they have changed between any Access versions so far. By
undocumented I mean that Microsoft hasn't made any official commitment
to maintaining their existing structure rather than lack of mention.

I don't think you can point and click your mouse and type in a single search
string to get the list of matching queries with a document object like you
can in a datasheet.




Yes, it's easier to point and click than it is to write VBA code for most
people.




It doesn't open in datasheet view so you can sort and filter with your mouse
and the toolbar.

Your three responses illustrate the reasons for using MSys tables well.

People can't change, add or delete anything in the system table when they
open it in datasheet view. They're looking at read-only data. Why is that a
bad practice?

The bad practice I'm talking about is relying on a particular structure
for the MSys tables when there's less of a guarantee of that structure
staying the same than for functionality that was designed into the
product for that purpose. If Microsoft has made a commitment to the
existing structure of MSys tables then by all means use them for the
reasons cited. If you know of such a statement please indicate where it
can be found.

James A. Fortune
(e-mail address removed)

 

[Granny Spitz via AccessMonster.com]

By
undocumented I mean that Microsoft hasn't made any official commitment
to maintaining their existing structure rather than lack of mention.

I think they've said they won't *support* use of system tables by users.
System tables are intended for use by Jet, so if we're using them in our code
or in the UI we're using them at our own risk.

If Microsoft has made a commitment to the
existing structure of MSys tables then by all means use them for the
reasons cited. If you know of such a statement please indicate where it
can be found.

Microsoft *won't* make such a statement because they won't paint themselves
into a corner. But there are some things about the system tables you *can*
rely on to some extent. (I say that because of the lawsuit that Microsoft
lost which caused them to remove the ability to modify linked spreadsheets
from Access. That feature was clearly documented and supported ... until it
wasn't.) Read what Michael Kaplan has to say about this:
http://www.trigeminal.com/usenet/usenet017.asp?1033.

 

[James A. Fortune]

I think they've said they won't *support* use of system tables by users.
System tables are intended for use by Jet, so if we're using them in our code
or in the UI we're using them at our own risk.




Microsoft *won't* make such a statement because they won't paint themselves
into a corner. But there are some things about the system tables you *can*
rely on to some extent. (I say that because of the lawsuit that Microsoft
lost which caused them to remove the ability to modify linked spreadsheets
from Access. That feature was clearly documented and supported ... until it
wasn't.) Read what Michael Kaplan has to say about this:
http://www.trigeminal.com/usenet/usenet017.asp?1033.

Granny,

Thanks for your input. I think we've clarified what we mean and I agree
with the points you've just made. Perhaps I should have said "risky"
practice rather than "bad" practice . The linked spreadsheet lawsuit
is in a different category, IMO, because Microsoft -- to their credit
-- tried to keep things from getting broken. If Microsoft changes the
structure of the MSys tables for a good reason I have no problem with
that. Sometimes I use unsupported concepts for the sake of convenience
also, but some are riskier than others. Hopefully, I'll rely on them
less and less over time. When things are done at our own risk it's good
to have an idea of what the risk is and to walk into it with our eyes open.

James A. Fortune
(e-mail address removed)

 

[RoyVidar]

The system tables are in Microsoft's KB articles, so if Microsoft
writes about them and posts it on the web I think they're officially
documented.

As in this article http://support.microsoft.com/kb/275563

having this warning;
"Method 2: Assigning Sorted System Table to Combo or List Box
Although this method will work with Microsoft Access 2000, it is not a
highly recommended method, as the structure of system tables may change
within future versions of Access."

Officially documented as being subject of change ;-)

 

[Granny Spitz via AccessMonster.com]

When things are done at our own risk it's good
to have an idea of what the risk is and to walk into it with our eyes open.

Yes, and the MSysObjects table structure is so fundamental to the Jet file
architecture that it's not that risky to use this table name and its column
names in code, at least from Access 95 through Access 2003. But using the
MSysObjects table structure will probably be more risky in future versions,
especially since Access 2007 can use the new AccDB file structure which might
not even have an MSysObjects table.

 

[Granny Spitz via AccessMonster.com]

Although this method will work with Microsoft Access 2000, it is not a
highly recommended method, as the structure of system tables may change
within future versions of Access."

Officially documented as being subject of change ;-)

Always wise to cover their butts. <g>

 

[dbahooker]

if you claim that 'its easier to point and click that write VBA code'

then you should be using Access Data Projects instead of your infested
MDB / DAO _CRAP_

how do you create a table in Access again?

 

[Van T. Dinh]

SELECT MSysObjects.Name
FROM MSysObjects
WHERE(MSysObjects.Type = -32764);

 

[Van T. Dinh]

You got me worried ... Glad you worked it our ...