>> List of database queries

[Jonathan]

Hi, using Access 2003 the following will list all queries in my database.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],1))<>"~"))
ORDER BY MSysObjects.Name;

The returned list includes pass-through queries. How do I acheive the same
result that does not include pass-throught queries?

Many thanks,
Jonathan

 

[pietlinden]

Hi, using Access 2003 the following will list all queries in my database.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],1))<>"~"))
ORDER BY MSysObjects.Name;

The returned list includes pass-through queries. How do I acheive the same
result that does not include pass-throught queries?

Many thanks,
Jonathan

Don't think you can do it that way... the PITA thing about it is that
you need to reference the query type...

Dim qdf as dao.querydef
for each qdf in CurrentDb.Querydefs
debug.print qdf.Name, qdf.Type
next qdf

If you check the querydefs with type dbQSQLPassThrough then you can
process them any way you want.

from the help:
dbQSPTBulk Used with dbQSQLPassThrough to specify a query that doesn't
return records (Microsoft Jet workspaces only).
dbQSQLPassThrough Pass-through (Microsoft Jet workspaces only)

 

[Alex Dybenko]

Hi,
you can check Flag field:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type=5 AND Left([Name],1)<>"~" AND not Flag in (112, 144)
ORDER BY MSysObjects.Name;

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Alex Dybenko]

Sorry, not Flag, but Flags:
http://accessblog.net/2009/12/how-to-list-queries-by-type.html

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi,
you can check Flag field:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type=5 AND Left([Name],1)<>"~" AND not Flag in (112,
144)
ORDER BY MSysObjects.Name;

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi, using Access 2003 the following will list all queries in my database.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],1))<>"~"))
ORDER BY MSysObjects.Name;

The returned list includes pass-through queries. How do I acheive the
same
result that does not include pass-throught queries?

Many thanks,
Jonathan

 

[Dale Fye]

I've never tried this, but I think the passthrough queries have to have a
Connection String assigned, so check the Connect and Database fields in
mSysObjects