query list

[Gabriel]

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

[Eric Schittlipz]

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

[Pieter Linden]

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.