How to determine which subqueries are used in a query

[KubixKiller]

Hi,

I'm an experienced MSA programmer since version 2... But now I need to
learn how someone else setup a certain database. The names of tables
and queries are terrible and not recognisable as either tables or
queries by their name.

Therefore, I want to write a piece of code that shows me whether an
item in the query is either a table or a (sub) query. Is there any way
to achieve this? I wrote the following code, but it only reveals the
end-table, not the subquery it came from.

By the way: I use MS Access 2000 SR1, and when renaming the queries to
solve the problem, I get errors because the automated object-rename
mechanism is not working in case expressions or cumulative functions
(SUM/MIN/MAX) are used.

So: any help is greatly appreciated!

--------------------------------------------------------------------------------------------------------------------------------------------------

Sub ShowQueryStructure(qryName As String, Optional level As Variant)
Dim db As database, qry As querydef
Dim tables As New Collection, fld As Field
Dim tName As Variant, tNameStr As String, x As Object

If IsMissing(level) Then
level = 0
End If

Set db = CurrentDb()
Set qry = db.QueryDefs(qryName)

For Each fld In qry.Fields
If IsNull(ItemInCollection(tables, fld.SourceTable)) And
(fld.SourceTable <> "") Then
tables.Add fld.SourceTable, fld.SourceTable
End If
Next fld

For Each tName In tables
tNameStr = CStr(tName)

Debug.Print Pad("", level * 3); tNameStr

On Error Resume Next

Set x = db.QueryDefs(tNameStr)
On Error GoTo 0
If Not (x Is Nothing) Then
ShowQueryStructure tNameStr, level + 1
End If
Next tName

qry.Close
db.Close
End Sub


--------------------------------------------------------------------------------------------------------------------------------------------------

 

[Michel Walsh]

Hi,


A name represent a table if it is in tabledefs, so to get all the table
name, you can read the collection, and to know if a name is a table, you can
check the name against that collection, or through error trapping (the first
one is probably preferable).

Generally there is no problem not knowing if we have a table or a query (or
a sub-query), when writing an SQL SELECT statement. And for that reason, I,
for one, does not advocate the use of a naming convention to differentiate
between them.

Hoping it may help,
Vanderghast, Access MVP

 

[KubixKiller]

Michel,

Thanks for the reply. The problem with using the field 'SourceTable' is
that even if a field is from a subquery, the underlying tablename is
reported, instead of the queryname. Mayby this helps in stating my
problem.

So: I also assumed what you wrote to be the solution, but it doesn't
seem to be working.....

Anyone else?

Greets Martin


Michel Walsh schreef: