How to determine which table uses the most space in an Access database?

This is actually an interesting problem because Access uses variable length records for storing it's data.

The best way to do this accurately would be to go through every record and every field of the table and add up the length of the fields. It could take a while if the tables are large. It wouldn't pick up the size due to indexes and relationships.

In our Total Access Analyzer program, we have a few reports that provide an estimate of the table size using a simple record size estimate times the number of records. An example is shown here: http://fmsinc.com/MicrosoftAccess/Documentation/Reports/Table_SizeBySize.html

That might be sufficient for ballpark estimates or relative size comparisons.

Another, and probably very accurate way to measure this would be to create a new database and export the table into it. Compact the database and subtract the blank database size from it to get the table's size.

I know this is an old post but I have a solution based on my own experience with the same question. My solution is to export all tables to text files. The size of each text file is roughly proportional to how much space it uses in the mdb/accdb file.

The code below will create a subfolder "temp_table_size" under the current database folder and export all of the tables to it. You can pass it a parameter to only process the local tables. When it is finished it tells you how many tables were exported and asks if you want to open the folder. Sort that folder by Size and you'll quickly identify the culprits. I use this routine to find tables that I may have forgotten to clear before deployment, or to help me understand where the big tables are when I inherit someone else's database.

To make this routine even more handy for me, I added this code to an Access add-in so I can run it against any database. That add-in also has features to export all other Access objects so I can see what forms/reports are taking up space in the database. Maybe I'll find a place to share it if there is any interest.

Public Sub DocDatabase_Table(Optional bolLocalTablesOnly As Boolean = False)
 '====================================================================
 ' Name:    DocDatabase_Table
 ' Purpose: Exports the tables in this database to a series of
 '          text files.  The size of each text file will give you
 '          an idea of what tables use the most disk space.
 '
 ' Author:  Ben Sacherich
 ' Date:    5/2/2011
 '====================================================================
    On Error GoTo ErrorHandler

    Dim dbs As Database ' or Variant if this fails.
    Dim td As TableDef
    Dim strSaveDir As String
    Dim lngObjectCount As Long
    Dim lngCount As Long
    Dim strMsg As String
    Dim varReturn As Variant

    Set dbs = CurrentDb() ' use CurrentDb() to refresh Collections

    ' Export to a subfolder of the current database folder.
    strSaveDir = CurrentProject.path & "\temp_table_size\"

    If Len(strSaveDir) > 0 Then

        strMsg = "This feature exports all of the tables in this database to a series of " _
            & "comma delimited text files.  The size of each text file will give you " _
            & "an idea of what tables use the most disk space." & vbCrLf & vbCrLf

        ' Get a count of the tables, minus the system tables.
        If bolLocalTablesOnly = True Then
            lngObjectCount = DCount("Name", "MSysObjects", "Type=1 AND Name not like 'MSys*' AND Name not like '~*'")
            strMsg = strMsg & "There are " & lngObjectCount & " local tables in this database. " _
                & vbCrLf & vbCrLf
        Else
            ' Include Local, Linked, and ODBC tables
            lngObjectCount = DCount("Name", "MSysObjects", "Type in (1,4,6) AND Name not like 'MSys*' AND Name not like '~*'")
            strMsg = strMsg & "There are " & lngObjectCount & " tables in this database " _
                & "(including local, linked, and ODBC)." & vbCrLf & vbCrLf
        End If
        strMsg = strMsg & "The tables will be exported to a subfolder of the current database:  " _
            & strSaveDir & vbCrLf & vbCrLf
        strMsg = strMsg & "Do you want to continue?"

        If MsgBox(strMsg, vbYesNo + vbInformation, "Export Tables") = vbYes Then

            If Dir(strSaveDir, vbDirectory) = "" Then
                MkDir strSaveDir
            End If

            ' Initialize and display message in status bar.
            varReturn = SysCmd(acSysCmdInitMeter, "(" & Format((lngCount) / lngObjectCount, "0%") & ")  Preparing tables", lngObjectCount)

            dbs.TableDefs.Refresh
            For Each td In dbs.TableDefs ' Tables
                If (bolLocalTablesOnly = True And Len(td.Connect) = 0) _
                  Or (bolLocalTablesOnly = False) Then

                    If Left(td.Name, 4) <> "MSys" And Left(td.Name, 1) <> "~" Then
                        Debug.Print td.Name, td.Attributes

                        ' Update message in status bar.
                        varReturn = SysCmd(acSysCmdSetStatus, "(" & Format((lngCount + 1) / lngObjectCount, "0%") _
                            & ")  Exporting table: " + td.Name)

                        DoCmd.TransferText acExportDelim, , td.Name, strSaveDir & "Table_" & td.Name & ".txt", True
                        lngCount = lngCount + 1

                    End If
                End If
            Next td

            'Remove the Progress Meter
            varReturn = SysCmd(acSysCmdRemoveMeter)

            If MsgBox("Exported " & lngCount & " object(s)." _
                & vbCrLf & vbCrLf & "Do you want to open the destination folder: " & strSaveDir & " ? " _
                , vbSystemModal + vbYesNo + vbInformation, "Table Size") = vbYes Then

                ' Open the output folder in Windows Explorer
                Call Shell("explorer.exe " & strSaveDir, vbNormalFocus)
            End If
        End If
    End If

Exit_Sub:
    Set td = Nothing
    Set dbs = Nothing

    Exit Sub

ErrorHandler:

    Debug.Print Err.Number, Err.Description
    Select Case Err
        Case "3011"
            MsgBox "Table '" & td.Name & "' could not be found or has a broken link." _
                & vbCrLf & vbCrLf & "Link: " & td.Connect _
                & vbCrLf & vbCrLf & "Click OK to continue.", vbExclamation, "Error 3011"
            Resume Next
        Case "75"
            ' This happens when you try to create a folder name that already exists.
            ' For this Q&D function, ignore the error.
            Resume Next
        Case Else
            MsgBox Err.Description
            Resume Next
    End Select

    GoTo Exit_Sub

End Sub

For functioning Access databases you can get the simple tool Access Memory Reporter 1.0 which shows the amount of memory the tables and indices needs. Note that I haven't tried this tool myself.

What is your objective once you discover the largest table? How large is your MDB? Have you compacted it recently?

How much does it shrink when you compact it? That is are you creating and deleting a lot of tables/records in it? If so see the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app.

Are you using a lot of graphics in the tables?