Identify unused tables

[Newbie5]

Is there a way to query any system tables to list all tables that are being referenced in any queries, forms, reports, modules, etc.? We've inherited a rather large Access application containing some tables that we suspect aren't being used at all by the application, and we'd like to verify this suspicion before deleting these tables.

TIA

 

[Ian Mac]

I don't know if there is an actual answer to your question.

But have you tried using the Documenter on the DB, print it out or view it and see what is what.

 

[allan57]

The following code will tell you where the table is used ie Form Record Source, Form VB Procedure, Report Record Source etc or leaves the field blank if the table is not used.


Create table:-

Application_Table_Where_Used_Analysis

With the following fields

txtTable_Name - Text
fTable_In_Use_By_DB – Boolean
txtWhere_Used_Type – Text
txtWhere_Used_Description – Text

Create a form with the following code in it:-

Private Sub Form_Load()

Dim dbs As Database

Dim frmGetActiveForm As Form
Dim frmGetActiveReport As Report
Dim tdf As TableDef

Dim ctrForms As Container
Dim ctrReports As Container
Dim ctrModules As Container

Dim mdlModules As Module
Dim mdlFormModules As Module
Dim mdlReportModules As Module

Dim docContainerName As document

Dim strCheckQueryIsUsedByDatabase As String
Dim strGetGetQueryName As String

Dim lngSLine As Long
Dim lngSCol As Long
Dim lngELine As Long
Dim lngECol As Long
    
Dim intRecordCounter As Integer
    
On Error Resume Next

    Set dbs = CurrentDb
    
    Set ctrForms = dbs.Containers!Forms
    Set ctrReports = dbs.Containers!Reports
    Set ctrModules = dbs.Containers!Modules
    
    DoCmd.RunSQL ("DELETE [Application_Table_Where_Used_Analysis].* FROM Application_Table_Where_Used_Analysis;")
    
    'Check Form Recordsource & Procedures
    For Each docContainerName In ctrForms.Documents
           
        DoCmd.OpenForm docContainerName.Name, acDesign
           
        Set frmGetActiveForm = Forms(docContainerName.Name)
        Set mdlFormModules = frmGetActiveForm.Module
        
        For Each tdf In dbs.TableDefs
       
            'Check Recordsource
            If Forms(docContainerName.Name).RecordSource = tdf.Name Then
               
                DoCmd.RunSQL ("INSERT INTO Application_Table_Where_Used_Analysis ( txtTable_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
                              "SELECT '" & tdf.Name & "', '" & docContainerName.Name & "', 'Form Record Source';")

            End If
             
            lngSLine = 0
            lngSCol = 0
            lngELine = 0
            lngECol = 0
            
            'Check Procedures
            If mdlFormModules.Find(tdf.Name, lngSLine, lngSCol, lngELine, lngECol) = True Then
               
               DoCmd.RunSQL ("INSERT INTO Application_Table_Where_Used_Analysis ( txtTable_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
                             "SELECT '" & tdf.Name & "', '" & docContainerName.Name & "', 'Form VB Procedure';")
            
            End If
        
        Next tdf
                     
        DoCmd.Close acForm, docContainerName.Name, acSaveNo
   
    Next docContainerName
    
    'Check Report Recordsource & Procedures
    For Each docContainerName In ctrReports.Documents
           
        DoCmd.OpenReport docContainerName.Name, acDesign
       
        Set frmGetActiveReport = Reports(docContainerName.Name)
        Set mdlReportModules = frmGetActiveReport.Module
    
        For Each tdf In dbs.TableDefs
       
            'Check Recordsource
            If Reports(docContainerName.Name).RecordSource = tdf.Name Then
               
                DoCmd.RunSQL ("INSERT INTO Application_Table_Where_Used_Analysis ( txtTable_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
                              "SELECT '" & tdf.Name & "', '" & docContainerName.Name & "', 'Report Record Source';")

            End If
           
            lngSLine = 0
            lngSCol = 0
            lngELine = 0
            lngECol = 0
            
            'Check Procedures
            If mdlReportModules.Find(tdf.Name, lngSLine, lngSCol, lngELine, lngECol) = True Then
               
               DoCmd.RunSQL ("INSERT INTO Application_Table_Where_Used_Analysis ( txtTable_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
                             "SELECT '" & tdf.Name & "', '" & docContainerName.Name & "', 'Report VB Procedure';")
            
            End If
        
        Next tdf
    
        DoCmd.Close acReport, docContainerName.Name, acSaveNo
       
    Next docContainerName
    
    'Check Module Code
    For Each docContainerName In ctrModules.Documents
           
        For Each tdf In dbs.TableDefs
       
           DoCmd.OpenModule docContainerName.Name
    
           Set mdlModules = Modules(docContainerName.Name)

           If mdlModules.Find(tdf.Name, lngSLine, lngSCol, lngELine, lngECol) = True Then
           
               DoCmd.RunSQL ("INSERT INTO Application_Table_Where_Used_Analysis ( txtTable_Name, txtWhere_Used_Description, txtWhere_Used_Type) " & _
                             "SELECT '" & tdf.Name & "', '" & docContainerName.Name & "', 'VB Module';")

           End If
                      
        Next tdf
    
        DoCmd.Close acModule, docContainerName.Name
                      
    Next docContainerName
    
        'Query is used by the database
        For Each tdf In dbs.TableDefs
    
        strCheckQueryIsUsedByDatabase = "Not Used"
        
        strCheckQueryIsUsedByDatabase = DLookup("[txtTable_Name]", "Application_Table_Where_Used_Analysis", "[txtTable_Name]='" & tdf.Name & "'")
        
        If strCheckQueryIsUsedByDatabase = "Not Used" Then
        
            DoCmd.RunSQL ("INSERT INTO Application_Table_Where_Used_Analysis ( txtTable_Name, fTable_In_Use_By_DB ) " & _
                          "SELECT '" & tdf.Name & "', False;")
        
        End If
        
    Next tdf
    
    dbs.Close
    
    Set frmGetActiveForm = Nothing
    Set mdlFormModules = Nothing
    Set docContainerName = Nothing
    Set ctrForms = Nothing
    Set ctrReports = Nothing
    Set ctrModules = Nothing
    Set dbs = Nothing

End Sub