using VBA to print out lists of query tables and fields

[Paul James]

I'm using the code below to print a list of all the queries in a database,
along with their fields, and it works fine. However, I would like to add
some code that would also display the table(s) which contain those fields.
In addition, it would also be great if I could also include code that would
print out a result showing any criteria in that field. (What I'm using this
for is to determine all fields in my queries that contain criteria, and use
that as a guide to index those fields to improve performance).

Public Sub EnumQueryFields()
Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As QueryDef, fld As Variant '(fld As Field) didn't work
Dim n As Integer
n = 0
For Each qdf In db.QueryDefs
For Each fld In qdf.Fields
If Not Left(qdf.Name, 1) = "~" Then
n = n + 1
Debug.Print n & "," & qdf.Name & "," & fld.Name
End If
Next fld
Next qdf
Set fld = Nothing
Set qdf = Nothing
End Sub

Thanks in advance,

Paul

 

[Douglas J. Steele]

You might have to read the SQL property of the query and parse it to be able
to do that.

OTOH, it's possible that FMS's Total Access Analyzer does that already.
http://www.fmsinc.com/products/analyzer/index.html

 

[Brendan Reynolds]

If you have references to both the DAO and ADO (ActiveX Data Objects)
libraries, then you can probably solve the problem you were having with Dim
fld As Field by specifying the DAO object library, e.g. Dim fld As
DAO.Field.

You can find the table that the field belongs to by examining the
SourceTable property of the Field object.

However, there is, to the best of my knowledge, no property that would tell
you whether the field is used in the criteria. Although the graphical query
designer may make it appear as though this is a property of a field, in
reality, a field forms part of the criteria of the query if it appears in
the WHERE clause of the query. You could examine the SQL property of the
query to determine whether the name of the field occurs after the occurrence
of the word 'WHERE' in the SQL, but determining the end of the WHERE clause
in a complex query will probably prove non-trivial.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Paul James]

Thank you, gentlemen.

I got the table names from the SourceTable property of the Field object.

Thanks also for the suggestion about the References Library and the link to
the Total Access Analyzer.

Parsing an SQL statement to locate a WHERE clause and it's following code is
beyond my ability, but with the Table names I can speed up the project
substantially.

 

[Ken Snell [MVP]]

Not a perfect solution, but the following subroutine will allow you to do
"searching" within a query's SQL string:

Public Sub FindQueriesThatUseATextString(strFindString As String)
Dim dbs As DAO.Database
Dim qdf As QueryDef
Dim intC As Integer
Set dbs = CurrentDb
For Each qdf In dbs.QueryDefs
If qdf.SQL Like "*" & strFindString & "*" Then Debug.Print qdf.Name
Next qdf
dbs.Close
End Sub

This would allow you to do something like this:

Call FindQueriesThatUseATextString("WHERE*MyFieldName")

 

[Paul James]

Thanks, Ken. I'll experiment with it.

Paul

 

[Paul James]

It works great, Ken.

I'm using the most general criteria search:

Call FindQueriesThatUseATextString("WHERE")

I want to find all queries that have any kind of criteria statement,
regardless of which fields might be included, because I'm trying to identify
all fields that are used as criteria fields in the query.

Thanks for your help (again).

Paul

 

[Ken Snell [MVP]]

Glad to have been of assistance.

 

[Paul James]

I just realized that I also need to run a search for queries that contain
the work "HAVING" in the SQL statement, if I'm trying to locate all queries
that have criteria fields.

Question: are there any other key words in Access SQL besides WHERE or
HAVING that establish criteria fields in queries withouth using WHERE or
HAVING?

Thanks in advance,

Paul

 

[Ken Snell [MVP]]

A bit "off track", but domain functions can be used in the expressions for
calculated fields to do some "filtering", though in a very limited way, of
the record's data.

Other than that, I think WHERE and HAVING will cover you.

 

[Brendan Reynolds]

If I remember rightly, the aim was to identify fields that need to be
indexed, right? If so, you also need to consider fields used in joins and
fields used in ORDER BY clauses.

The tricky bit is not so much identifying where a particular clause begins,
as identifying where it ends. For example the end of a WHERE clause may be
marked by the end of the SQL statement, by the beginning of a GROUP BY
clause, or by the beginning of an ORDER BY clause. (Not necessarily an
exhaustive list). This is what I meant earlier about this being
'non-trivial'!

Sounds like an interesting challenge, though.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Paul James]

domain functions can be used in the expressions for calculated fields to

do some "filtering"

Good point. Thanks for mentioning that.

I'm trying to eliminate domain aggregate functions from my queries, because
I've been told that they can slow things down significantly. However, I
don't know if that applies to domain functions in calculated fields, or just
in the query criter expression.

Do you have any insight into that?

 

[Paul James]

If I remember rightly, the aim was to identify fields that need to be

indexed, right? If so, you also need to consider fields used in joins and
fields used in ORDER BY clauses.

Oh gosh, I forgot about joins and sorted fields.

From a time standpoint, I think I'll catalog the fields manually. By the
time I figure out how to catch every possibility in my VBA expression, I
could have gotten the information the old fashioned way.

Thanks for pointing that out.

 

[Ken Snell [MVP]]

Any inclusion of a domain function in a query will slow the query
significantly. Doesn't matter where it's being used....

For queries with lots of data, I usually write a separate query to produce
the lookup values that I then join to in the query.. usually *much* faster
results that way.

 

[Paul James]

usually *much* faster

results that way.

Yes, I suspected that. I'm planning on replacing my domain functions with
queries.

Thanks.