find out which tables/queries are used by query 1

[JeroenNL]

Hi there,

I want to find out (in code) which tables and/or queries are used by a certain query. Anybody knows a good way to accomplish this?

Thanks,
Jeroen

 

[RickSpr]

You'll have to parse the SQL string, which you obtain from the SQL property of the QueryDef object.

The following procedure is one I wrote to do the initial parsing. It will get you as far as extracting the FROM clause. You'll have to add code to parse the FROM clause itself. To be completely general, it will have to pick out the initial table name, plus any table names that follow the JOIN keyword, and it will have to deal with the possibility that table names are [bracketed] and subexpressions are in (parentheses). NOTE: This procedure does not support UNION queries.

Private Sub ParseSQLSelect(sql As String, _
                    Optional ByRef FieldList As Variant, _
                    Optional ByRef FromClause As Variant, _
                    Optional ByRef WhereClause As Variant, _
                    Optional ByRef OrderByClause As Variant, _
                    Optional ByRef GroupByClause As Variant, _
                    Optional ByRef HavingClause As Variant)
' Purpose: Breaks a SQL SELECT statement into its components.
' Accepts: The SQL query string to parse
' Returns: Strings containing the various clauses, without their intro keywords
'          1. The list of fields in the SELECT clause
'          2. The table expression from the FROM clause
'          3. The criteria from the WHERE clause, if present, else ""
'          4. The ordering specs from the ORDER BY clause, if present, else ""
'          5. The list of fields from the GROUP BY clause, if present, else ""
'          6. The criteria from the HAVING clause, if present, else ""
' Remarks: This version supports only SELECT statements without embedded subqueries.
    Dim stmt As String
    Dim b As Integer, f As Integer
    Dim w As Integer, g As Integer, h As Integer, o As Integer, S As Integer
    Dim i As Integer
    
    ' Copy the string, trimming leading and trailing blanks
    ' and making sure it ends with a ";"
    stmt = Trim$(sql) & ";"
    
    ' Translate any CR or LF characters into spaces
    Do
        i = InStr(i + 1, stmt, vbCr)
        If i = 0 Then Exit Do
        Mid$(stmt, i, 1) = " "
    Loop
    Do
        i = InStr(i + 1, stmt, vbLf)
        If i = 0 Then Exit Do
        Mid$(stmt, i, 1) = " "
    Loop
    
    ' If this query has parameters, delete the PARAMETERS clause
    If Left$(stmt, 11) = "PARAMETERS " Then _
        stmt = Mid$(stmt, InStr(stmt, "SELECT "))
    
    ' Now truncate at the first ";"
    stmt = Left$(stmt, InStr(1, stmt, ";"))
    
    ' Verify that the statement starts with a SELECT clause, and doesn't
    ' contain the UNION keyword. This procedure can't parse the statement
    ' otherwise
    If UCase$(Left$(stmt, 7)) <> &quot;SELECT &quot; Then Exit Sub
    If InStr(stmt, &quot;UNION &quot;) > 0 Then Exit Sub
    
    ' Find the starts of the various clauses, skipping over the keywords
    b = InStr(1, stmt, &quot;SELECT &quot;, vbTextCompare): i = b + 7
    f = InStr(i, stmt, &quot;FROM &quot;, vbTextCompare): If f > 0 Then i = f + 5
    w = InStr(i, stmt, &quot;WHERE &quot;, vbTextCompare): If w > 0 Then i = w + 6
    g = InStr(i, stmt, &quot;GROUP BY &quot;, vbTextCompare): If g > 0 Then i = g + 9
    h = InStr(i, stmt, &quot;HAVING &quot;, vbTextCompare): If h > 0 Then i = h + 7
    o = InStr(i, stmt, &quot;ORDER BY &quot;, vbTextCompare): If o > 0 Then i = o + 9
    S = Len(stmt)
    
    ' For each missing clause, set its start to the start of the following clause
    If o = 0 Then o = S
    If h = 0 Then h = o
    If g = 0 Then g = h
    If w = 0 Then w = g
    If f = 0 Then f = w
    
    ' Now extract the clauses
    b = b + 7
    If Not IsMissing(FieldList) Then _
        FieldList = Trim$(Mid$(stmt, b, f - b))
    If f < w Then f = f + 5
    If Not IsMissing(FromClause) Then _
        FromClause = Trim$(Mid$(stmt, f, w - f))
    If w < g Then w = w + 6
    If Not IsMissing(WhereClause) Then _
        WhereClause = Trim$(Mid$(stmt, w, g - w))
    If g < h Then g = g + 9
    If Not IsMissing(GroupByClause) Then _
        GroupByClause = Trim$(Mid$(stmt, g, h - g))
    If h < o Then h = h + 7
    If Not IsMissing(HavingClause) Then _
        HavingClause = Trim$(Mid$(stmt, h, o - h))
    If o < S Then o = o + 9
    If Not IsMissing(OrderByClause) Then _
        OrderByClause = Trim$(Mid$(stmt, o, S - o))
End Sub

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[randysmid]

Hi,
This code may be useful. Place it inside a module. You can modify it to check for a specific query. As you can see, this code will show all queries, then send the info to a text file on the "C" drive.

Public Sub GetQueryObjects()
Dim db As Database
Dim qry As QueryDef
Dim Fld As Field
Set db = CurrentDb
Open "c:\TESTqueryList.txt" For Output As #1
For Each qry In db.QueryDefs
Print #1, qry.Name
For Each Fld In qry.Fields
Write #1, " - - " & Fld.Name & ", " & Fld.SourceTable
Next Fld
Next qry
Close #1

End Sub
HTH,
Randy Smith
California Teachers Association

 

[RickSpr]

Of course! For some reason my mind wasn't on stored queries; I assumed Jeroen was talking about SQL statements (another meaning of 'query').

Jeroen, if you're talking about stored queries, Randy's code is much better. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[randysmid]

Rick,
Your code to parse a query is awesome, and I have added it to my library. Many thanks, and a star for your efforts.
HTH,
Randy Smith
California Teachers Association

 

[MichaelRed]

Sql String:

SELECT MSysObjects.Name, MSysQueries.Attribute, MSysQueries.Expression, MSysQueries.Name1, MSysQueries.Name2
FROM MSysQueries LEFT JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE (((MSysObjects.Name)=[Enter Query Name]))
WITH OWNERACCESS OPTION;

Query Name entered: "qryVend"

[b]The SQL statement / String of Query Vend[/b]
[code]
SELECT DISTINCTROW tblVend.Vend, tblVend.VendName
FROM tblVend
ORDER BY tblVend.Vend
WITH OWNERACCESS OPTION;

The results Set from the SQL String

Name[tab][tab]Attribute[tab]Expression[tab]Name1[tab]Name2
qryVend[tab][tab]0
qryVend[tab][tab]255
qryVend[tab][tab]6[tab][tab][tblVend].[Vend]		
qryVend[tab][tab]6[tab][tab][tblVend].[VendName]	
qryVend[tab][tab]5[tab][tab]tblVend	
qryVend[tab][tab]11[tab][tab][tblVend].[Vend]		
qryVend[tab][tab]3
[/code

While 'code' is good, I would expect that a brief persual would show the requested information (and a bit more) is provided with a simple (parameter) query.  The "Source" objects are (obvioously?) listed in [Name1] & [Name2].  Review / study of the other fields returned may be of interest for other purposes.

It follows basically from some of the more basic rules of relational db, specifically that the schema should be available from within the (relational) data base, although Ms. Version is, perhaps, less straight forward than some.

 MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeroenNL]

Hi guys,

Thanks for your replies, they all have been VERY helpfull! I think I can use all code provided by you.

I do have a question for MichealRed though... I don't quite understand your reply. Care to explain in a little more detail?

Thanks,
Jeroen

 

[MichaelRed]

Sigh, ... Grumble, ... Groan, ...

hopefully not.

the only real clues I will offer:

A[tab]strictly speaking I did NOT answer the 'question', as there is really no code here;

B[tab]if you can, copy the "Sql String" from my post and paste it into an EMPTY query (create a "NEW" query without any source selected)

switch to the SQL view.

Rember the name of SOME query in your db.

"RUN" the newly generated query

enter the name of your 'rembered' query in the parameter pop-up box.

Review the results set. The display (query grid) will be formatted properly and be quite a bit easier to interpert.

Return / re-post with specific questions

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeroenNL]

Hi,

Thanks for your explanation MichealRed. I tried your suggestions and I see what you mean now.

Randysmid:
I've been experimenting with your code and have a few questions.

1: The field definition is of type Field, but my database doesn't recognize the SourceTable property. What am I doing wrong?

2. This solution gives me a list of sourcetables, but I would like to have a list of queries and tables which are being used in a particular query. How to achieve that?

Thanks again! I'm learning a lot from this thread,
Jeroen

 

[JeroenNL]

After looking at MichealRed's suggestion a bit close, I think I should look at attribute 5 in combination with the Name1 field. Would that be sufficient in ALL cases?

Furthermore, I would still like to achieve my goal in code; some hybrid method seems appropiate so any suggestion is welcome!

Bye,
Jeroen

 

[MichaelRed]

since you seem intent on a 'code' soloution, this will be my last post to the thread.

your observation appears -to me- to be accurate, but incomplete.

if you want to persue the knowledge, i would suggest you run the created query aginst a number of queries with various levels of complexity and different types.

someone else posted a partial explination of the fields of the MSysQueries table fairly recently. a brief review thereof revealed no errors in the interpertation from my own previous interpertation.

a caveat in the process /technique is that, as a "system" table, it is subject to change by ms w/o announcement, and as far as i know, it remains (offically) undocumented.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeroenNL]

Hi again,

It took quite some time but I finally got the results I wanted. I wanted to generate a treeview of all queries and tables being used by a query. Like this:

[tt]
qrymaak_OnvolledigPerDag_InternNB
- qryCPInUse_EANInternNB
---- qryCPInUse
-------- ConnectionPoints
---- tblPARAMETERS
- qryValid_EANInternNB
---- qryCPInUse_EANInternNB
-------- qryCPInUse
------------ ConnectionPoints
-------- tblPARAMETERS
---- qryDayTotalsValid_PerDatum
-------- qryDayTotalsValid
------------ DayTotals
-------- tblPARAMETERS
[/tt]

The code to produce such a treeview for ALL queries in the current database is listed below:

Public Function FindQueryObjects(strQueryNaam As String, indent As Integer) As Boolean

Dim qry As QueryDef
Dim rst As Recordset
Dim strspaces As String
Dim qrynaam As String
Dim i As Integer

strspaces = ""
For i = 1 To indent
strspaces = strspaces & "-"
Next
strspaces = strspaces & " "

Set rst = New Recordset
rst.Open "SELECT MSysQueries.Attribute, MSysQueries.Expression, " & _
"MSysQueries.Name1, MSysQueries.Name2 FROM MSysQueries LEFT JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id " & _
"WHERE (MSysObjects.Name = '" & strQueryNaam & "') And (MSysQueries.Attribute = 5) WITH OWNERACCESS OPTION;", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rst.MoveFirst
While Not rst.EOF
If Not IsNull(rst.Fields("Name1&quot) Then
qrynaam = rst.Fields("Name1&quot
Print #1, strspaces & qrynaam
If QueryBestaat(qrynaam) Then
indent = indent + 4
FindQueryObjects qrynaam, indent
indent = indent - 4
End If
End If
rst.MoveNext
Wend

Set rst = Nothing

End Function

Public Function MakeQueryList() As Boolean

Dim qry As QueryDef
Dim rst As Recordset
Dim indent As Integer
Dim strQueryNaam As String

indent = 4

Open "c:\TESTqueryList.txt" For Output As #1

For Each qry In CurrentDb.QueryDefs

strQueryNaam = qry.Name

Print #1, strQueryNaam

Set rst = New Recordset
rst.Open "SELECT MSysQueries.Attribute, MSysQueries.Expression, " & _
"MSysQueries.Name1, MSysQueries.Name2 FROM MSysQueries LEFT JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id " & _
"WHERE (MSysObjects.Name = '" & strQueryNaam & "') And (MSysQueries.Attribute = 5) WITH OWNERACCESS OPTION;", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rst.MoveFirst
While Not rst.EOF
If Not IsNull(rst.Fields("Name1&quot) Then
strQueryNaam = rst.Fields("Name1&quot
Print #1, "- " & strQueryNaam
If QueryBestaat(strQueryNaam) Then FindQueryObjects strQueryNaam, indent
End If
rst.MoveNext
Wend
Set rst = Nothing

Next

Close #1

End Function


I thought someone could find this usefull too so. Let me know if it was. Suggestions to improve the code are also welcome as this is just the first working version.

Bye and thanks,
Jeroen

 

[JeroenNL]

Hi,

Sorry, I forgot to include the following function. QueryBestaat means QueryExists in dutch.

Public Function QueryBestaat(strQueryNaam As String) As Boolean

Dim found As Boolean
Dim qry As AccessObject

found = False
For Each qry In CurrentData.AllQueries
If qry.Name = strQueryNaam Then
found = True
End If
Next

QueryBestaat = found

End Function

Bye,
Jeroen

 

[CindyK]

Hi all - just noticed this thread, and for what it's worth, the beta version (Access 11) has a feature called dependencies which allows you to select any object and see all the objects that depends upon it, and/or that it depends upon.....

Of course, that doesn't necessarily mean it will work reliably ;( but worth keeping an eye on once the software is out and being used...

Cindy K.