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

J

Jim Moberg

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*.
 
V

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.
 
J

Jim Moberg

What does the ~ represent?

Van T. Dinh said:
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.
Click to expand...
 
V

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.
 
P

Pieter Wijnen

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

Pieter
 
V

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.
 
J

James A. Fortune

Granny said:
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.
Click to expand...

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
[email protected]
 
D

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
 
J

James A. Fortune

Granny said:
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.
Click to expand...

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.
Click to expand...

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?
Click to expand...

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
[email protected]
 
J

James A. Fortune

Granny said:
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.
Click to expand...

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
[email protected]
 
R

RoyVidar

Granny Spitz via AccessMonster.com said:
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.
Click to expand...

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 ;-)
 
D

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?
 
T

Thomas [PBD]

Great! I got this to work and it does exactly what I need it to do.
However, is there a way to make the queries selectable from a Form? I have
many databases with many queries, and I would like to give the users the
ability to select the queries that they need based on the Switchboard/Forms
that I have set into place. I was thinking of somehow making them hyperlinks
to follow to the actual queries, but dont know if that is entirely possible.
 
Top