What queries use certain table?

[Access_help_rob]

hey im trying to find out what queries reports or table etc use data or have links to a certain table in my database, is there a quick way to do this or is it a manual job of going through all queries, table, report? thanks for any help
rob

 

[dan-cat]

Yowsas,

I guess its possible by looping through each query within your db and checking the SQL property to see if it contains your table name. I suppose you could do the same with the record source of all your reports in theory.

I'm not sure if Access provides an easier way.

Is it really going to be a drag to do this manually?

 

[Mile-O]

hey im trying to find out what queries reports or table etc use data or have links to a certain table in my database, is there a quick way to do this or is it a manual job of going through all queries, table, report? thanks for any help
rob

I can tell you how to get a list of all the tables used in any/every query by using DAO. Do you want any or every?

Also, what about queries used within comboboxes, listboxes, and RowSources of form and report objects?

And do you use any naming conventions within your database? If so, what and are you consistent in that area?

 

[Access_help_rob]

hey mile, alls i have done is go to design view of queries and see if there is a link to that table, i have done all of them now so it should be ok cheers mate
rob

 

[dan-cat]

I'll have a butchers at that code Mr Phile please

 

[Mile-O]

Public Function QueryInfo() As Boolean

    On Error GoTo Err_QueryInfo
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim qdf As DAO.QueryDef
    Dim fld As DAO.Field
    
    Set db = CurrentDb
    Set tdf = db.CreateTableDef("tblTablesInQueries")
    With tdf
        .Fields.Append .CreateField("QueryName", dbText, 255)
        .Fields.Append .CreateField("ComponentName", dbText, 255)
    End With
    
    db.TableDefs.Append tdf
    
    Set rs = db.OpenRecordset(tdf.Name)
    
    For Each qdf In db.QueryDefs
        For Each fld In qdf.Fields
            If Not Left(qdf.Name, 1) = "~" Then
                If DCount("ComponentName", "tblTablesInQueries", _
                    "QueryName = """ & qdf.Name & """ AND ComponentName = """ & fld.SourceTable & """") = 0 Then
                    With rs
                        .AddNew
                        .Fields("QueryName") = qdf.Name
                        If Not fld.SourceTable = vbNullString Then .Fields("ComponentName") = fld.SourceTable
                        .Update
                    End With
                End If
            End If
        Next
    Next
    
    rs.Close
    db.Close

Exit_QueryInfo:
    Set fld = Nothing
    Set qdf = Nothing
    Set tdf = Nothing
    Set rs = Nothing
    Set db = Nothing
    Exit Function
    
Err_QueryInfo:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_QueryInfo
    
End Function

 

[dan-cat]

Mile,

I got all that apart from the line

If Not Left(qdf.Name, 1) = "~" Then

is this simply a check for queries that begin with ~

 

[Mile-O]

Mile,

I got all that apart from the line

If Not Left(qdf.Name, 1) = "~" Then

is this simply a check for queries that begin with ~

Queries stored in combos, listboxes, etc. have names like:

~sql_blablabla_cboMyCombo

 

[Cosmos75]

Nice one, Miles!!