Reporting on Access database query hierarchy

T

Tom

Hi there. I've written some (very bad!) Visual Basic that trawls through the
Access data dictionary tables and produces a hierarchical report of all
queries and what other queries/base tables each one uses, with what join
criteria and so on. It works, but runs *very* slowly (because it's written so
badly). Does anybody have anything that does a similar job but is written
properly, recursively? I'm happy to post my code but it's pretty 'orrible. It
depends on having some queries set up on the dictionary which start with
"ZZZ" - if anyone seriously wants to implement my code (it takes all day to
produce a 4-meg file on a database with several hundred queries in it), then
get in touch and I'll give you the query defs too!

My code below;

Option Compare Database

Public Function TreeWalk()

Dim myDB As Database

Set myDB = DBEngine.Workspaces(0).Databases(0)

Dim myRS As Variant

Dim myRS2 As Variant

Dim myRS3 As Variant

Dim myRS4 As Variant

Dim myRS5 As Variant

Dim myRS6 As Variant

Dim myRS7 As Variant

Dim myRS8 As Variant

Dim QueryName As String

Dim QueryQuery As String

Dim ChildName As String

Dim CodeString As String

Dim IndentName As String

Set myRS = myDB.OpenRecordset("select [name] from [ZZZ top level queries]
where [name] not like '*sq_*' and [name] not like 'ZZZ*' and [name] not like
'*__*'")

Set fs = CreateObject("Scripting.FileSystemObject")

Set a = fs.CreateTextFile("c:\temp\doc.txt", True)

' Level 1

Do Until myRS.EOF

QueryName = myRS("[Name]")

a.writeline (Chr(12))

a.writeline ("QUERY " + QueryName)

myRS.MoveNext

' Insert here the code for fetching the queried columns out

QueryQuery = "select [expression] from [zzz query columns] where [name]
like '" + QueryName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline ("COLUMN " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query join criteria] where
[name] like '" + QueryName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline ("JOIN " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query compare criteria]
where [name] like '" + QueryName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline ("COMPARE " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query grouping] where [name]
like '" + QueryName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline ("GROUP " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query having] where [name]
like '" + QueryName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline ("HAVING " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query order] where [name]
like '" + QueryName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline ("ORDER " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close
' End of code for fetching the queried columns out

QueryQuery = "select [name1] from [ZZZ Self join] where [name] like '" +
QueryName + "'"

Set myRS2 = myDB.OpenRecordset(QueryQuery)

' Level 2

Do Until myRS2.EOF

ChildName = myRS2("[name1]")

IndentName = " QUERY " + ChildName

a.writeline (IndentName)
' Insert here the code for fetching the queried columns out

QueryQuery = "select [expression] from [zzz query columns] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" COLUMN " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query join criteria] where
[name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" JOIN " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query compare criteria]
where [name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" COMPARE " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query grouping] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" GROUP " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query having] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" HAVING " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query order] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" ORDER " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close
' End of code for fetching the queried columns out

myRS2.MoveNext

QueryQuery = "select [name1] from [ZZZ Self join] where [name] like
'" + ChildName + "'"

Set myRS3 = myDB.OpenRecordset(QueryQuery)

' Level 3

Do Until myRS3.EOF

ChildName = myRS3("[name1]")

IndentName = " QUERY " + ChildName

a.writeline (IndentName)
' Insert here the code for fetching the queried columns out

QueryQuery = "select [expression] from [zzz query columns] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" COLUMN " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query join criteria] where
[name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" JOIN " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query compare criteria]
where [name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" COMPARE " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query grouping] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" GROUP " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query having] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" HAVING " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query order] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" ORDER " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close
' End of code for fetching the queried columns out

myRS3.MoveNext

QueryQuery = "select [name1] from [ZZZ Self join] where [name]
like '" + ChildName + "'"

Set myRS4 = myDB.OpenRecordset(QueryQuery)

'Level 4

Do Until myRS4.EOF

ChildName = myRS4("[name1]")

IndentName = " QUERY " + ChildName

a.writeline (IndentName)
' Insert here the code for fetching the queried columns out

QueryQuery = "select [expression] from [zzz query columns] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" COLUMN " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query join criteria] where
[name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" JOIN " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query compare criteria]
where [name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" COMPARE " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query grouping] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" GROUP " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query having] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" HAVING " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query order] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" ORDER " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close
' End of code for fetching the queried columns out

myRS4.MoveNext

QueryQuery = "select [name1] from [ZZZ Self join] where
[name] like '" + ChildName + "'"

Set myRS5 = myDB.OpenRecordset(QueryQuery)

' Level 5

Do Until myRS5.EOF

ChildName = myRS5("[name1]")

IndentName = " QUERY "
+ ChildName

a.writeline (IndentName)
' Insert here the code for fetching the queried columns out

QueryQuery = "select [expression] from [zzz query columns] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" COLUMN " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query join criteria] where
[name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" JOIN " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query compare criteria]
where [name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" COMPARE " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query grouping] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" GROUP " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query having] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" HAVING " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query order] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" ORDER " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close
' End of code for fetching the queried columns out

myRS5.MoveNext

QueryQuery = "select [name1] from [ZZZ Self join] where
[name] like '" + ChildName + "'"

Set myRS6 = myDB.OpenRecordset(QueryQuery)

' Level 6

Do Until myRS6.EOF

ChildName = myRS6("[name1]")

IndentName = "
QUERY " + ChildName

a.writeline (IndentName)
' Insert here the code for fetching the queried columns out

QueryQuery = "select [expression] from [zzz query columns] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" COLUMN " +
CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query join criteria] where
[name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" JOIN " +
CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query compare criteria]
where [name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" COMPARE " +
CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query grouping] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" GROUP " +
CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query having] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" HAVING " +
CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query order] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" ORDER " +
CodeString)

myRS8.MoveNext

Loop

myRS8.Close
' End of code for fetching the queried columns out

myRS6.MoveNext

QueryQuery = "select [name1] from [ZZZ Self join]
where [name] like '" + ChildName + "'"

Set myRS7 = myDB.OpenRecordset(QueryQuery)

'Level 7

Do Until myRS7.EOF

ChildName = myRS7("[name1]")

IndentName = "
QUERY " + ChildName

a.writeline (IndentName)
' Insert here the code for fetching the queried columns out

QueryQuery = "select [expression] from [zzz query columns] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" COLUMN
" + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query join criteria] where
[name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" JOIN
" + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query compare criteria]
where [name] like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline ("
COMPARE " + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query grouping] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" GROUP
" + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query having] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" HAVING
" + CodeString)

myRS8.MoveNext

Loop

myRS8.Close

QueryQuery = "select [expression] from [zzz query order] where [name]
like '" + ChildName + "'"

Set myRS8 = myDB.OpenRecordset(QueryQuery)

Do Until myRS8.EOF

CodeString = myRS8("[Expression]")

a.writeline (" ORDER
" + CodeString)

myRS8.MoveNext

Loop

myRS8.Close
' End of code for fetching the queried columns out

myRS7.MoveNext

Loop

myRS7.Close

Loop

myRS6.Close

Loop

myRS5.Close

Loop

myRS4.Close

Loop

myRS3.Close

Loop

myRS2.Close

Loop

myRS.Close

a.Close

End Function


Public Function AppendSubqueries(QueryName As String)

Dim myRS2 As Recordset

Dim QueryQuery As String

Dim IndentName As String

Dim ChildName As String

QueryQuery = "select [name1] from [ZZZ Self join] where [name] like '" +
QueryName + "'"

Set myRS2 = myDB.OpenRecordset(QueryQuery)

Do Until myRS2.EOF

ChildName = myRS2("[name1]")

IndentName = " " + ChildName

a.writeline (IndentName)

myRS2.MoveNext

Loop

myRS2.Close

End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Improving treeview load time 13
stuck in a loop 5
OBJECTIVE: Print PDF to file, each record should be a file name =PID (field) - have code issue belo 2
can't find strSQL 1
OpenRecordset problem (parameter query) 3
tweak this for me...please 2
Error 3071 when trying to do query 2
Send Object Help 2

Top