Tables in Use

[Thales750]

Is there a way to report all the table that are in queries or forms. Some of them will be in VBA queries.

I know this is a long shot...

 

[Ranman256]

paste this code into a module, then in the debugger window (ctl-G) type:
FindInSql "tMyTable"

it will then show all queries using the parameter given.

Public Sub FindInSql(pvFind, Optional ByVal pvQType)
'Dim db As Database
Dim qdf As QueryDef
Dim vFind
Dim sSql As String
'qtype can be..
Const kQS = 0
Const kQD = 32
Const kQU = 48
Const kQA = 64
Const kQM = 80
Const kQN = 128
Const kQX = 16
Const kQpt = 112   'dbQSQLPassThrough

On Error GoTo ErrFind
vFind = pvFind

'Set db = CurrentDb
Debug.Print "-- Start qry find for:" & pvFind
'Debug.Print "qry type FILTER:="

For Each qdf In CurrentDb.QueryDefs
    sSql = qdf.SQL
    qdf.Close
    If InStr(sSql, vFind) > 0 Then
       If IsMissing(pvQType) Then
          Debug.Print getQryTypeNam(qdf.Type); ": "; qdf.Name
       Else
          If qdf.Type = pvQType Then Debug.Print getQryTypeNam(qdf.Type); ": "; qdf.Name             ', "..FLT SRCH"
       End If
    End If
Next
Debug.Print "--End qry find"
Set qdf = Nothing
'Set db = Nothing
Exit Sub

ErrFind:
Debug.Print "error: " & qdf.Name
Resume Next
End Sub



 'qry types in text
Public Function getQryTypeNam(pvTypeNum)
Select Case pvTypeNum
    Case 0 '= select
       getQryTypeNam = "sel"
    Case 16 '= xtab
       getQryTypeNam = "xtab"
    Case 32 '= del
       getQryTypeNam = "del"
    Case 48 '= upd
       getQryTypeNam = "upd"
    Case 64 '= appd
       getQryTypeNam = "apd"
    Case 80 '= make
       getQryTypeNam = "make"
    Case 128 '= union
       getQryTypeNam = "union"
End Select
End Function

 

[isladogs]

Code like that in post #2 will give you part of what you need but not everything.
Add Case =3 to get the 'temp' queries see in form/report record sources and in combo/listbox row sources.

You could also get a list of all dependant objects using the database documenter.

However, none of the above will search the objects referenced in VBA code.

For that you need a utility like V-Tools by skrol (free) or Find and Replace by Access Dev Tools (commercial) .
I use both of them and they are both excellent

 

[jdraw]

Here is some older vba code to review forms and identify the recordsources to a table.
Not exactly what your asked but should be a start for your "or forms".

'---------------------------------------------------------------------------------------
' Procedure : PutFormRecordSourcesInTable
' Author    : Jack
' Date      : 02/03/2014
' Purpose   : Review all forms in this database; find recordsource for each.
' If no recordsource bypass the form.
' if recordsource is a Table/Query identify the table/query and fields.
' if recordsource is SQL, identify the SQL string.
'---------------------------------------------------------------------------------------
'
Sub PutFormRecordSourcesInTable()
          Dim afrm As AccessObject
          Dim frm As Access.Form
          Dim Db As DAO.Database
          Dim rs As DAO.Recordset
          Dim RecSourceType As String
          Dim strSQL_Drop As String
          Dim strSQL_Create As String

10        On Error Resume Next
20        Set Db = CurrentDb
          'Delete existing copy of this table
30        strSQL_Drop = "DROP TABLE tblRecordSourceOfForms;"

40        DoCmd.RunSQL strSQL_Drop
50        On Error GoTo PutFormRecordSourcesInTable_Error
60        strSQL_Create = "CREATE TABLE tblRecordSourceOfForms" & _
                          " (form_name varchar(250), RecordSourceType varchar(20),RecordSourceText longtext,RecordedDate Date  );"
          
70        Db.Execute strSQL_Create, dbFailOnError

80        DoEvents
90        Set rs = Db.OpenRecordset("tblRecordSourceOfForms")

100       For Each afrm In CurrentProject.AllForms

110           If Not afrm.IsLoaded Then DoCmd.OpenForm afrm.name, acDesign, , , , acHidden

120           If Len(Forms(afrm.name).RecordSource & "") = 0 Then
130               Debug.Print afrm.name & "  -- " & "**NO ASSIGNED RECORDSOURCE**"
140               RecSourceType = "NONE"
150           ElseIf InStr(Trim(Forms(afrm.name).RecordSource), "SELECT ") > 0 Then
160               Debug.Print afrm.name & "  -- " & "  -    SQL      - " & Forms(afrm.name).RecordSource
170               RecSourceType = "SQL"
180           Else
190               Debug.Print afrm.name & "  -- " & "  - Table/Query - " & Forms(afrm.name).RecordSource
200               RecSourceType = "Table/Query"
210           End If
220           rs.AddNew
230           rs!form_name = afrm.name
240           rs!RecordSourceType = RecSourceType
250           rs!RecordSourceText = Trim(Forms(afrm.name).RecordSource)
260           rs!RecordedDate = Date
270           rs.Update
GetNext:
280           DoCmd.Close acForm, afrm.name
290       Next afrm
300       Debug.Print "Finished processing " & Now
310       rs.Close
320       On Error GoTo 0
330       Exit Sub

PutFormRecordSourcesInTable_Error:

340       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure PutFormRecordSourcesInTable of Module AWF_Related"

End Sub

 

[Pat Hartman]

On the Database Tools ribbon, choose Object Dependencies. This will prompt you to turn on Name AutoCorrect. That is fine but when you are finished, for your own sanity, turn it off again. This will give you a map for each object you click on to see where it is used.

NOTHING built in will search VBA which is one of the many reasons I almost never use embedded SQL. You are on your own there and will need to search using an add in utility such as find and replace. I'm not even sure that Total Access Analyzer will find embedded SQL and map it by table and field.

 

[Cotswold]

Maybe if you used a simple naming system, obviously starting with your tables, as all database systems start there. For over 40 years I have used numbers in the range 10 to 99 which have worked well for me. From there, part of your table names would then transfer through to your queries, forms and reports. Making your system self documenting.

 

[ebs17]

I know this is a long shot...

If you can't find the right code or tool, you can build something yourself. It's not even very difficult, just a diligent task in designing the details.

- You can find all tables in the table listing of the backend or in MSysObjects.

- With Application.SaveAsText you can export the complete definitions of all frontend objects (forms, reports, modules, classes, queries and even macros) individually as text files. Text files with plain text can be easily searched (Instr).
Embedded SQL and domain aggregate functions can thus also be evaluated.

- Now only the list of tables has to be compared with the list of text files and an evaluation created that meets your own requirements.