getting Tags ID used in queries

Hi,

I have hundreds of queries using thousand of tags recorded by the access
database.
From all these queries, I need to create a list of all the Tags used and to
know in which queries they are used and from which table they are recorded.

Right now, i am opening the SQL view of the queries and copy/paste in Excel
all the original tag names (FieldSource) together with the table (fieldTable)
where they are recording.

Is their a simpler / half-automatic method to get lists of all the Tag name?

Thank you!

On 14.12.2009 10:40, Kami wrote:
Hi,

I have hundreds of queries using thousand of tags recorded by the access
database.
From all these queries, I need to create a list of all the Tags used and to
know in which queries they are used and from which table they are recorded.

Right now, i am opening the SQL view of the queries and copy/paste in Excel
all the original tag names (FieldSource) together with the table (fieldTable)
where they are recording.

Is their a simpler / half-automatic method to get lists of all the Tag name?
Use the CurrentDb.QueryDefs collection to enumerate them, e.g.

Dim qd As DAO.QueryDef

For Each qd In CurrentDbC.QueryDefs
Debug.Print qd.Name; qd.SQL
' qd.Open
' enum qd.Fields for field names.
' qd.Close
Next qd


mfG
-- stefan --

I have never heard of tags associated with a query? Do you mean fields?

If so, you can use VBA to loop through all the queries and get a list of the
fields that are returned. You would still have problems with nested queries,
subqueries, and fields used in joins or the where clause. I'm not sure what
crosstab queries would show.

If you alias a field you will get that name returned for the field's name
property.

You would probably need a commercial tool of some kind to really get good results.

Products to document the database and do a global find and replace. All have
Access 97 and later versions. Some don't have an Access 2003/2007 version,
but may still work with Access 2003/2007.

Shareware (Try and Buy):
Find and Replace http://www.rickworld.com

Commercial (Try and Buy)
Speed Ferret http://www.moshannon.com

Commercial
Total Access Analyzer http://www.fmsinc.com

Free (For Access XP Only??):
http://www3.bc.sympatico.ca/starthere/findandreplace


Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora: http://www3.bc.sympatico.ca/starthere/findandreplace/ (Free)
V-Tools: http://www.skrol29.com/us/vtools.php (Free)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Kami wrote:
Hi,

I have hundreds of queries using thousand of tags recorded by the access
database.
From all these queries, I need to create a list of all the Tags used and to
know in which queries they are used and from which table they are recorded.

Right now, i am opening the SQL view of the queries and copy/paste in Excel
all the original tag names (FieldSource) together with the table (fieldTable)
where they are recording.

Is their a simpler / half-automatic method to get lists of all the Tag name?

Thank you!

Hi,
Thanks for your answer.
Your code is not working, "enum qd.Fields " gives an error.
Kami

"Stefan Hoffmann" wrote:

On 14.12.2009 10:40, Kami wrote:
Hi,

I have hundreds of queries using thousand of tags recorded by the access
database.
From all these queries, I need to create a list of all the Tags used and to
know in which queries they are used and from which table they are recorded.

Right now, i am opening the SQL view of the queries and copy/paste in Excel
all the original tag names (FieldSource) together with the table (fieldTable)
where they are recording.

Is their a simpler / half-automatic method to get lists of all the Tag name?
Use the CurrentDb.QueryDefs collection to enumerate them, e.g.

Dim qd As DAO.QueryDef

For Each qd In CurrentDbC.QueryDefs
Debug.Print qd.Name; qd.SQL
' qd.Open
' enum qd.Fields for field names.
' qd.Close
Next qd


mfG
-- stefan --
.