By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,918 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

query list

P: n/a
Hi,

The following will generate a list of all tables and queries froma db:

SELECT MSysObjects.Type, MSysObjects.Name, MSysObjects.DateCreate,
MSysObjects.DateUpdate
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) AND
((Left([name],4))="MSys"))
ORDER BY MSysObjects.Type, MSysObjects.DateCreate;
I just wanted to know if there is a way to have the type of query
(append, update, etc) on the list? Any hint ?

Thank you in advance
Gabriel
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Gabriel" <ga*************@yahoo.com> wrote in message
news:3a**************************@posting.google.c om...
Hi,

The following will generate a list of all tables and queries froma db:

SELECT MSysObjects.Type, MSysObjects.Name, MSysObjects.DateCreate,
MSysObjects.DateUpdate
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) AND
((Left([name],4))="MSys"))
ORDER BY MSysObjects.Type, MSysObjects.DateCreate;
I just wanted to know if there is a way to have the type of query
(append, update, etc) on the list? Any hint ?

Thank you in advance
Gabriel

I think you will need to try another approach such as using the DAO.QueryDef
object so you can inspect the .SQL property of each query.
Nov 13 '05 #2

P: n/a
ga*************@yahoo.com (Gabriel) wrote in message news:<3a**************************@posting.google. com>...
Hi,

The following will generate a list of all tables and queries froma db:

SELECT MSysObjects.Type, MSysObjects.Name, MSysObjects.DateCreate,
MSysObjects.DateUpdate
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) AND
((Left([name],4))="MSys"))
ORDER BY MSysObjects.Type, MSysObjects.DateCreate;
I just wanted to know if there is a way to have the type of query
(append, update, etc) on the list? Any hint ?

Thank you in advance
Gabriel


Short of your creating prefixes for them when you name your queries,
no. Otherwise, you'll have to do what Eric said. (I'd almost swear
that's DM in disguise...) You could loop through the QueryDefs
collection, pass the SQL property to a function, parse it a little
inside the function and then return a query type. Not the most
scientific, but it should get you started. Might be worthwhile if you
have a zillion queries to catalog. Definitely a time to use some kind
of naming conventions... especially when you have a lot of objects.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.