Tables used in query

[RickP]

Is there a way to find out all the tables used in a saved access query? Currently I do something like:

Dim qry As DAO.QueryDefs

Set qry = db.QueryDefs

For i = 0 To qry.Count - 1
        tvDBObjects.Nodes.Add "QUERIES", tvwChild, qry(i).Name, qry(i).Name
        For j = 0 To qry(i).Fields.Count - 1
            If PrevTable <> qry(i).Fields(j).SourceTable Then
                tvDBObjects.Nodes.Add qry(i).Name, tvwChild, , qry(i).Fields(j).SourceTable
                PrevTable = qry(i).Fields(j).SourceTable
                DoEvents
            End If
        Next
        DoEvents
    Next

But this doesn't always work. ie I have a make table query and it didn't return any SourceTables for it. Any help would be great thanks.

-Rick

[RobDog888]

The .SourceTable property will return the first level of tables/queries
for your tabledef object. Now if you have a form that has a
recordsource of only a table, you will return a querydef of "~sq_f" + the form name.

I take it that your are trying to map a series of nested queries
and place them into a treeview control?

Try this...

Option Explicit
'Add reference to DAO
Private Sub Command1_Click()
 
    Dim qry As DAO.QueryDefs
    Dim db As DAO.Database
    Dim i As Integer
    
    Set db = DAO.OpenDatabase("D:\RobDog888.mdb")
    Set qry = db.QueryDefs
    
    For i = 0 To qry.Count - 1
        If InStr(1, qry(i).Name, "~sq_f") = 0 Then  'Actual queries only
            Debug.Print qry(i).Name; qry(i).Type
            Debug.Print qry(i).Properties(20).Value 'Parse this property to get the tables
            DoEvents                                'Actual queries do not have a fields collection,
                                                    'only form recordsources do. fields = textboxes
                                                    'on form, so to speak.
                                                    'Prop 20 is the DOL prop.
        End If
    Next
 
End Sub
 
'Output from my db below line - Tables: tblTest, Table1. Queries: Query1, Query2
'-------------------------------------------------------------------------------------------
'Query1 0 
'??  ????????  ????    [color=red]Table1[/color]   ????????  ????    [color=red]tblTest[/color]   ????????....truncated because it is field data after this
'Query2 0 
'??  ????????  ????    [color=red]Query1[/color]   ????????  ????    [color=red]Table1[/color]   ????????....truncated because it is field data after this

HTH

[RickP]

I take it that your are trying to map a series of nested queries
and place them into a treeview control?

Kindof. I want to be able to access which tables are used within each query. Then Which queries are used within each Report, and what is all used in a Macro. We are trying to break some macros out of 1 DB and put in it's own DB. The macro is large and I don't want to manually find all the information needed by this macro. So I am trying to create something where you can just click the macro, and do an export to another db and it will copy everything that is needed over to the new DB.

Properties(20).Value gives me an item not found in this collection.
Properties(17).Values gives me:

??  ????????  ???????tblNEWCUSTOM

Which seems to be right. Why is yours different? I'm using Access 2000 SP-3. What is that above anyway? Why all the ?'s?



[EDIT]

Plus some have 0 instead of string.
Plus it seems it goes table name, columns used, table name, columns used, etc.. Parsing out the SQL statement would be easier. I would just think MS would give an easy way to see which tables are in the query, seeing how int Access it shows them graphically.

[RobDog888]

Yes, I'm using 2003. Could be the difference, but your get the error
when you dont filter out the forms. The "?"s are access specific
dataidentifiers. Something like a GUID pointer to the actual query
object. so if yuo parse out the table/query names until the
identifer which specifies that fields in the query, you can have a
list of all the tables that the query contains at the first level only.

HTH

[RickP]

Does this work for all query types? Make table, update, etc... ? That seems to be where I get the 0, cause I am filtering out.

so if yuo parse out the table/query names until the
identifer which specifies that fields in the query, you can have a
list of all the tables that the query contains at the first level only.

I guess I don't follow you on this. Since it returns tablename, then the columns used from that table, then more tablenames, the only way I can make sure I'm getting a table and not a column is by checking each string against the current tables (doesn't seem very effecient). Plus when you say first level what do you mean? Do you mean if a query called a query? Cause that would show (like your example), and my logic would include that query to export and that query would then be looked at. Major point now is how would you parse that string? Thanks for your help on this btw.

-Rick

[RobDog888]

In mine, it shows all tables first then all fields.
Here is an update that is a little better.

Option Explicit
'Add reference to DAO
Private Sub Command1_Click()
 
    Dim qry As DAO.QueryDefs
    Dim db As DAO.Database
    Dim i As Integer
    
    Set db = DAO.OpenDatabase("D:\RobDog888.mdb")
    Set qry = db.QueryDefs
    
    For i = 0 To qry.Count - 1
        If InStr(1, qry(i).Name, "~sq_f") = 0 Then  'Actual queries only
            Debug.Print qry(i).Name; qry(i).Type
            Debug.Print qry(i).Properties(qry(i).Properties.Count - 1).Value 'Parse this property to get the tables
            DoEvents                                'Actual queries do not have a fields collection,
                                                    'only form recordsources do. fields = textboxes
                                                    'on form, so to speak.
                                                    'Prop 20 is the DOL prop.
        End If
    Next
 
End Sub
 
 
'Output:
'qryMakeTableQry 80 
'??  ????????  ????    Table1   ???????? ????????Test2   ???????? ????????gfhs                    
'Query1 0 
'??  ????????  ????    Table1   ????????  ????    tblTest   ???????? ????????Test2   ???????? ????????Field1   ???????? ????????Field2   ???????? ????????Field3   ???????? ????????Field4   ???????? ????????gfhs                    
'Query2 0 
'??  ????????  ????    Query1   ????????  ????    Table1   ???????? ????????Test2   ???????? ????????Field1   ???????? ????????Field2   ???????? ????????Field3   ???????? ????????Test2            

The first  on mine is designating the end of the tables/queries
in the query. Then the rest are fields.

I also made a make-table query and it worked too.

[RobDog888]

Ok, replaced the for loop with this and I dont know why the
question amrk can not be found with either a replace or an
instr

If I copy one ? and in the immediate window do a ?Asc("?") is
tells me that its a ascii character 63. Co if I do an Instr on chr(63)
it still doesnt work.

If Dir("D:\DB Query Map.txt") <> vbNullString Then Kill "D:\DB Qyery Map.txt"
    For i = 0 To qry.Count - 1
        If InStr(1, qry(i).Name, "~sq_f") = 0 Then  'Actual queries only
            'Debug.Print qry(i).Name; qry(i).Type
            'Debug.Print qry(i).Properties(qry(i).Properties.Count - 1).Value
            Open "D:\DB Qyery Map.txt" For Append As #1
                Print #1, qry(i).Name; qry(i).Type
                Print #1, "----------------------------------------"
                sTemp = qry(i).Properties(qry(i).Properties.Count - 1).Value
                Print #1, Trim$(Replace(sTemp, Chr(63), "")) 'Isnt working for some reason????
            Close #1
                                                    'Parse this property to get the tables
            DoEvents                                'Actual queries do not have a fields collection,
                                                    'only form recordsources do. fields = textboxes
                                                    'on form, so to speak.
                                                    'Prop 20 is the DOL prop.
        End If
    Next