Determine which tables are no longer used (1 Viewer)

CedarTree

Registered User.
Local time
Today, 02:46
Joined
Mar 2, 2018
Messages
438
Hello - inherited an Access database with lots of tables that I suspect are not being used. How can I "audit" which tables are not not being referred to in any queries, modules, etc. so that we can slim down the DB (I would move the un-used tables to a backup DB just in case of course). Thanks!
 
Last edited:
The Access Database Documenter would be a good starting point.
1731003731250.png

One method I've used is to rename all suspect tables from say, tblYourBestChoices to Z_tblYourBestChoices. Run the database through all of its functions. If something fails because of a missing table (form's recordsource, queries, etc.), then you rename it back and move on.
 
Another built in method is to view the object dependencies for each table
However, you can only do that with one table at a time

If you have a large number of tables or databases to check, you might find my database analyzer useful:

You could also check out other third party tools including:
 
One method I've used is to rename all suspect tables from say, tblYourBestChoices to Z_tblYourBestChoices. Run the database through all of its functions. If something fails because of a missing table (form's recordsource, queries, etc.), then you rename it back and move on.
This only works if you have Name Autocorrect turned off. With NAC on, Access will help you out and fix up the links to use the "new" table name.
 
This only works if you have Name Autocorrect turned off. With NAC on, Access will help you out and fix up the links to use the "new" table name.
Yes, but who leaves Name Autocorrect turned on? On purpose, that is?;)
 
I do indeed … even when managing this specific situation. :D
Before I start, I normally check object dependencies as already mentioned.

Even with NAC switched on, you can still identify tables which aren't used in a similar way to what George suggested..
Instead of renaming the tables, make a copy with a suitable prefix e.g. z_ then delete the original.
NAC doesn't apply to the copied table so you can then test all queries, form record sources (etc) as suggested in post #2
If something breaks, remove the z_ prefix to restore functionality, again without disabling NAC
 
Last edited:
chatgpt has this beautiful answer (VBA):
Code:
Sub CheckUnusedTables()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim qdf As DAO.QueryDef
    Dim doc As AccessObject
    Dim frm As Form
    Dim rpt As Report
    Dim tableName As String
    Dim tableUsed As Boolean
    Dim i As Integer

    Set db = CurrentDb()

    ' Loop through each table in the database
    For Each tdf In db.TableDefs
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then ' Ignore system and temporary tables
            tableName = tdf.Name
            tableUsed = False

            ' Check queries for table usage
            For Each qdf In db.QueryDefs
                If InStr(1, qdf.SQL, tableName, vbTextCompare) > 0 Then
                    tableUsed = True
                    Exit For
                End If
            Next qdf

            ' Check forms for table usage
            If Not tableUsed Then
                For Each doc In CurrentProject.AllForms
                    DoCmd.OpenForm doc.Name, acDesign, , , , acHidden
                    Set frm = Forms(doc.Name)
                    For i = 0 To frm.RecordSource Like "*" & tableName & "*"
                        If InStr(1, frm.RecordSource, tableName, vbTextCompare) > 0 Then
                            tableUsed = True
                            Exit For
                        End If
                    Next i
                    DoCmd.Close acForm, doc.Name
                    If tableUsed Then Exit For
                Next doc
            End If

            ' Check reports for table usage
            If Not tableUsed Then
                For Each doc In CurrentProject.AllReports
                    DoCmd.OpenReport doc.Name, acDesign, , , acHidden
                    Set rpt = Reports(doc.Name)
                    If InStr(1, rpt.RecordSource, tableName, vbTextCompare) > 0 Then
                        tableUsed = True
                        Exit For
                    End If
                    DoCmd.Close acReport, doc.Name
                    If tableUsed Then Exit For
                Next doc
            End If

            ' Print unused table name
            If Not tableUsed Then
                Debug.Print "Unused Table: " & tableName
            End If
        End If
    Next tdf

    ' Clean up
    Set tdf = Nothing
    Set qdf = Nothing
    Set db = Nothing
End Sub
 
chatgpt has this beautiful answer (VBA):
I don't see how the code checks if a table is used by a function in a module or class or not.
I have several tables that are only used by some user defined functions.

And a note to anyone who's going to test it:
Add a DoEvents somewhere in the code. If you have a lot of objects, there's no way to stop the running process and you have to wait a very long time for the process to stop.
 
Last edited:
again from chatpt, additional code for code in Form, Report, Class Module and Standard module:
Code:
Option Compare Database
Option Explicit

' chatgpt
'
Sub CheckUnusedTables()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim qdf As DAO.QueryDef
    Dim doc As AccessObject
    Dim frm As Form
    Dim rpt As Report
    Dim tableName As String
    Dim tableUsed As Boolean
    Dim i As Integer

    Set db = CurrentDb()

    ' Loop through each table in the database
    For Each tdf In db.TableDefs
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then ' Ignore system and temporary tables
            tableName = tdf.Name
            tableUsed = False

            ' Check queries for table usage
            For Each qdf In db.QueryDefs
                If InStr(1, qdf.SQL, tableName, vbTextCompare) > 0 Then
                    tableUsed = True
                    Exit For
                End If
            Next qdf

            ' Check forms for table usage
            If Not tableUsed Then
                For Each doc In CurrentProject.AllForms
                    DoCmd.OpenForm doc.Name, acDesign, , , , acHidden
                    Set frm = Forms(doc.Name)
                    For i = 0 To frm.RecordSource Like "*" & tableName & "*"
                        If InStr(1, frm.RecordSource, tableName, vbTextCompare) > 0 Then
                            tableUsed = True
                            Exit For
                        End If
                    Next i
                    DoCmd.Close acForm, doc.Name
                    If tableUsed Then Exit For
                Next doc
            End If

            ' Check reports for table usage
            If Not tableUsed Then
                For Each doc In CurrentProject.AllReports
                    DoCmd.OpenReport doc.Name, acDesign, , , acHidden
                    Set rpt = Reports(doc.Name)
                    If InStr(1, rpt.RecordSource, tableName, vbTextCompare) > 0 Then
                        tableUsed = True
                        Exit For
                    End If
                    DoCmd.Close acReport, doc.Name
                    If tableUsed Then Exit For
                Next doc
            End If

            ' Print unused table name
            If Not tableUsed Then
                tableUsed = CheckTableReference(tableName)
            End If
            If Not tableUsed Then
                Debug.Print "Unused Table: " & tableName
            End If
        End If
    Next tdf

    ' Clean up
    Set tdf = Nothing
    Set qdf = Nothing
    Set db = Nothing
End Sub

'another from chatgpt
Function CheckTableReference(ByVal tableName As String) As Boolean
    Dim vbProj As VBIDE.VBProject
    Dim vbComp As VBIDE.VBComponent
    Dim codeMod As VBIDE.CodeModule
    Dim lineNum As Long
    Dim numLines As Long
    Dim codeLine As String
    'Dim tableName As String
    Dim isFound As Boolean
    
    ' Set the name of the table you are looking for
    'tableName = "YourTableName"
    isFound = False

    ' Set the current VBA project
    Set vbProj = Application.VBE.VBProjects(1) ' Assuming you have only one project open

    ' Loop through all modules
    For Each vbComp In vbProj.VBComponents
        ' Check if the component has a code module
        If vbComp.Type = vbext_ct_StdModule Or vbComp.Type = vbext_ct_ClassModule Or vbComp.Type = vbext_ct_Document Then
            Set codeMod = vbComp.CodeModule
            numLines = codeMod.CountOfLines
            
            ' Loop through each line of code
            For lineNum = 1 To numLines
                codeLine = codeMod.Lines(lineNum, 1)
                
                ' Check if the table name is referenced in the line
                If InStr(1, codeLine, tableName, vbTextCompare) > 0 Then
                    'Debug.Print "Table '" & tableName & "' found in Module: " & vbComp.Name & " at line " & lineNum
                    isFound = True
                End If
            Next lineNum
        End If
    Next vbComp

    ' Notify if the table reference was not found
    'If Not isFound Then
    '    MsgBox "Table '" & tableName & "' was not found in any module.", vbInformation
    'Else
    '    MsgBox "Search completed. Check the Immediate window for details.", vbInformation
    'End If
    CheckTableReference = isFound
End Function
 
I’ve not tested the code above but, assuming it works, it will cover many but not all of the use cases. Macros are still not covered.
See if you can get Chat GPT to inspect macro XML as well.
The same issue applies to the object dependencies feature

However utilities such as the Access Dependency Checker and V-Tools do check all objects
 
again, asking chatgpt for help.
it now scan all macros.
Code:
Option Compare Database
Option Explicit

' chatgpt
'
Sub CheckUnusedTables()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim qdf As DAO.QueryDef
    Dim doc As AccessObject
    Dim frm As Form
    Dim rpt As Report
    Dim tableName As String
    Dim tableUsed As Boolean
    Dim i As Integer

    Set db = CurrentDb()

    ' Loop through each table in the database
    For Each tdf In db.TableDefs
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then ' Ignore system and temporary tables
            tableName = tdf.Name
            tableUsed = False

            ' Check queries for table usage
            For Each qdf In db.QueryDefs
                If InStr(1, qdf.SQL, tableName, vbTextCompare) > 0 Then
                    tableUsed = True
                    Exit For
                End If
            Next qdf

            ' Check forms for table usage
            If Not tableUsed Then
                For Each doc In CurrentProject.AllForms
                    DoCmd.OpenForm doc.Name, acDesign, , , , acHidden
                    Set frm = Forms(doc.Name)
                    For i = 0 To frm.RecordSource Like "*" & tableName & "*"
                        If InStr(1, frm.RecordSource, tableName, vbTextCompare) > 0 Then
                            tableUsed = True
                            Exit For
                        End If
                    Next i
                    DoCmd.Close acForm, doc.Name
                    If tableUsed Then Exit For
                Next doc
            End If

            ' Check reports for table usage
            If Not tableUsed Then
                For Each doc In CurrentProject.AllReports
                    DoCmd.OpenReport doc.Name, acDesign, , , acHidden
                    Set rpt = Reports(doc.Name)
                    If InStr(1, rpt.RecordSource, tableName, vbTextCompare) > 0 Then
                        tableUsed = True
                        Exit For
                    End If
                    DoCmd.Close acReport, doc.Name
                    If tableUsed Then Exit For
                Next doc
            End If
            
            ' check modules and class
            If Not tableUsed Then
                tableUsed = CheckTableReference(tableName)
            End If
            ' check macros
            If Not tableUsed Then
                tableUsed = CheckTableInMacros(tableName)
            End If
            ' Print unused table name
            If Not tableUsed Then
                Debug.Print "Unused Table: " & tableName
            End If
        End If
    Next tdf

    ' Clean up
    Set tdf = Nothing
    Set qdf = Nothing
    Set db = Nothing
End Sub

'another from chatgpt
'check all modules, classes
Function CheckTableReference(ByVal tableName As String) As Boolean
    Dim vbProj As VBIDE.VBProject
    Dim vbComp As VBIDE.VBComponent
    Dim codeMod As VBIDE.CodeModule
    Dim lineNum As Long
    Dim numLines As Long
    Dim codeLine As String
    'Dim tableName As String
    Dim isFound As Boolean
    
    ' Set the name of the table you are looking for
    'tableName = "YourTableName"
    isFound = False

    ' Set the current VBA project
    Set vbProj = Application.VBE.VBProjects(1) ' Assuming you have only one project open

    ' Loop through all modules
    For Each vbComp In vbProj.VBComponents
        ' Check if the component has a code module
        If vbComp.Type = vbext_ct_StdModule Or vbComp.Type = vbext_ct_ClassModule Or vbComp.Type = vbext_ct_Document Then
            Set codeMod = vbComp.CodeModule
            numLines = codeMod.CountOfLines
            
            ' Loop through each line of code
            For lineNum = 1 To numLines
                codeLine = codeMod.Lines(lineNum, 1)
                
                ' Check if the table name is referenced in the line
                If InStr(1, codeLine, tableName, vbTextCompare) > 0 Then
                    'Debug.Print "Table '" & tableName & "' found in Module: " & vbComp.Name & " at line " & lineNum
                    isFound = True
                End If
            Next lineNum
        End If
    Next vbComp

    ' Notify if the table reference was not found
    'If Not isFound Then
    '    MsgBox "Table '" & tableName & "' was not found in any module.", vbInformation
    'Else
    '    MsgBox "Search completed. Check the Immediate window for details.", vbInformation
    'End If
    CheckTableReference = isFound
End Function


' again from chatgpt
' check macros
'
Function CheckTableInMacros(ByVal tableName As String) As Boolean
    Dim macroObj As AccessObject
    Dim found As Boolean
    Dim db As Object
    Dim tmpFile As String
    Dim content As String
    tmpFile = Environ$("temp") & "\Temp\TmpMcr.txt"
    
    Set db = CurrentProject
    found = False
    
    ' Loop through all macros in the database
    For Each macroObj In db.AllMacros
        
        'arnelgp
        'save macro to disk
        Application.SaveAsText acMacro, macroObj.Name, tmpFile
        
        'read the content
        content = ReadTextFile(tmpFile)
        found = InStr(1, content, tableName)
        If found Then Exit For
    Next macroObj
    
    'If Not found Then
    '    Debug.Print "Table '" & tableName & "' was not found in any macros."
    'End If
    Set db = Nothing
    CheckTableInMacros = found
End Function

' chatgpt
Function ReadTextFile(ByVal filePath As String) As String
    Dim fileNumber As Integer
    Dim fileContent As String
    Dim line As String

    ' Get a free file number
    fileNumber = FreeFile

    ' Open the file for input
    Open filePath For Input As #fileNumber

    ' Read the file line by line
    Do While Not EOF(fileNumber)
        Line Input #fileNumber, line
        fileContent = fileContent & line & vbCrLf
    Loop

    ' Close the file
    Close #fileNumber

    ' Print the content to the Immediate Window (Debug Window)
    'Debug.Print fileContent
    ReadTextFile = fileContent
End Function
 
Very neat, but too many Temps in file name?

tmpFile = Environ$("temp") & "\Temp\TmpMcr.txt"

This works
tmpFile = Environ$("temp") & "\TmpMcr.txt"
 
I've also just tested the code from post #12 on a large database initially with no unused tables.
I also noted the error & fix as pointed out by @Gasman - this is in the CheckTableInMacros function

I then added 3 test tables not used anywhere in the database
Initially it failed to detect these but adding DoEvents e.g. before the line Next tdf solved that issue (as well as allowing a breakpoint as mentioned by @KitaYama

I then added a relationship between 2 of those tables with R.I.
Both tables were still marked as unused. Whilst strictly correct, I think it would be better to exclude any unused tables involved in relationships
 
Both tables were still marked as unused. Whilst strictly correct, I think it would be better to exclude any unused tables involved in relationships
i believe what the op is after, is removing unused tables from the db as described on post #1.
so with correction, the code suffices the requirement.
 
I've also just tested the code from post #12 on a large database initially with no unused tables.
I also noted the error & fix as pointed out by @Gasman - this is in the CheckTableInMacros function

I then added 3 test tables not used anywhere in the database
Initially it failed to detect these but adding DoEvents e.g. before the line Next tdf solved that issue (as well as allowing a breakpoint as mentioned by @KitaYama

I then added a relationship between 2 of those tables with R.I.
Both tables were still marked as unused. Whilst strictly correct, I think it would be better to exclude any unused tables involved in relationships
I would think the safer approach would be to report any potentially unused tables, and leave it to the developer to decide how to handle each such table.
 
However utilities such as the Access Dependency Checker and V-Tools do check all objects
The Dependency Checker does not check VBA so if you are inclined to use embedded SQL rather than querydefs, none of your queries will be identified. I do think that V-Tools probably checks code but I don't have that installed to check it.
 
The built-in Object Dependencies feature doesn't check module code or macros.

However, I was referring to the freeware Access Dependency Checker which I originally mentioned (with a link) in post #3.
That does check all Access objects (as does the deep search feature in V-Tools).

1731176627288.png
 
I would think the safer approach would be to report any potentially unused tables, and leave it to the developer to decide how to handle each such table.
I'd go with that. Of course if a table is involved in a relationship, you will be warned before trying to delete it.
Either way, the code from ChatGPT does a good job of identifying potentially unused tables.
However, it would still be wise to check for issues before deleting all the tables it identifies.
 
I'd go with that. Of course if a table is involved in a relationship, you will be warned before trying to delete it.
Either way, the code from ChatGPT does a good job of identifying potentially unused tables.
However, it would still be wise to check for issues before deleting all the tables it identifies.
Hence my recommendation to rename suspect tables, and not rely on Name AutoCorrect while doing so.
 

Users who are viewing this thread

Back
Top Bottom