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

Jim Moberg wrote:
> 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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

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.

--
HTH
Van T. Dinh
MVP (Access)



"Jim Moberg" <(E-Mail Removed)> wrote in message
news:A987671D-B5AC-4CAB-91D7-(E-Mail Removed)...
>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*.

What does the ~ represent?

"Van T. Dinh" wrote:

> 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.
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Jim Moberg" <(E-Mail Removed)> wrote in message
> news:A987671D-B5AC-4CAB-91D7-(E-Mail Removed)...
> >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*.
>
>
>

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.

--
HTH
Van T. Dinh
MVP (Access)



"Jim Moberg" <(E-Mail Removed)> wrote in message
news:46BCC9D4-5954-4565-BD41-(E-Mail Removed)...
> What does the ~ represent?
>

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

Pieter

"Van T. Dinh" <(E-Mail Removed)> wrote in message
news:%23sG2AMU$(E-Mail Removed)...
> 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.
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Jim Moberg" <(E-Mail Removed)> wrote in message
> news:46BCC9D4-5954-4565-BD41-(E-Mail Removed)...
>> What does the ~ represent?
>>
>
>

--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 5402 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!

Thanks for this info. Where is the description stored?

"Pieter Wijnen" wrote:

> you can always include a join to MSysQueries instead of the type=5 argument
> ;-)
>
> Pieter
>
> "Van T. Dinh" <(E-Mail Removed)> wrote in message
> news:%23sG2AMU$(E-Mail Removed)...
> > 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.
> >
> > --
> > HTH
> > Van T. Dinh
> > MVP (Access)
> >
> >
> >
> > "Jim Moberg" <(E-Mail Removed)> wrote in message
> > news:46BCC9D4-5954-4565-BD41-(E-Mail Removed)...
> >> What does the ~ represent?
> >>
> >
> >
>
> --------------------------------------------------------------------------------
> I am using the free version of SPAMfighter for private users.
> It has removed 5402 spam emails to date.
> Paying users do not have this message in their emails.
> Try SPAMfighter for free now!
>
>
>

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.

--
HTH
Van T. Dinh
MVP (Access)



"Jim Moberg" <(E-Mail Removed)> wrote in message
news:A123E857-5D4B-455A-87F9-(E-Mail Removed)...
> Thanks for this info. Where is the description stored?
>

Granny Spitz via AccessMonster.com wrote:
> Jim Moberg wrote:
>
>>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.
>

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 Removed)

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



On Nov 3, 1:45 pm, "Van T. Dinh"
<VanThien.D...@discussions.microsoft.com> wrote:
> 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.
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Jim Moberg" <JimMob...@discussions.microsoft.com> wrote in messagenews:A123E857-5D4B-455A-87F9-(E-Mail Removed)...
>
>
>
> > Thanks for this info. Where is the description stored?- Hide quoted text -- Show quoted text -