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

Is there any easy way to determine how much space each table in an Access 2007 database is using?

I have an unusually big Access database and need to find out which table uses the most space. The row counts do not give enough information about the u开发者_运维百科sed space.

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

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.

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?

That's my approach: 1. Collect all non-system tables in database. 2. Export each table to a temporary database and compare size before and after. 3. Show the table with the collected information and delete the temporary database.

Anyway this is only an estimation because it is hard to calculate size exactly due to relations, unicode compression etc.

Copy this Sub to a global module and run it with F5:

Sub CheckTableSize()
    ' Table Size Analysis
    Dim DB As DAO.Database, NewDB As String, T As DAO.TableDef, SizeAft As Long, _
        SizeBef As Long, RST As DAO.Recordset, F As Boolean, RecCnt As Long

    Const StTable As String = "_Tables"

    Set DB = CurrentDb

    NewDB = Left(DB.Name, InStrRev(DB.Name, "\")) & Replace(Str(Now), ":", "-") & " " & _
        Mid(DB.Name, InStrRev(DB.Name, "\") + 1, Len(DB.Name))
    Application.DBEngine.CreateDatabase NewDB, DB_LANG_GENERAL

    F = False
    For Each T In DB.TableDefs
        If T.Name = StTable Then
            F = True: Exit For
        End If
    Next T
    If F Then
        DB.Execute "DELETE FROM " & StTable, dbFailOnError
    Else
        DB.Execute "CREATE TABLE " & StTable & _
            " (tblName TEXT(255), tblRecords LONG, tblSize LONG);", dbFailOnError
    End If

    For Each T In DB.TableDefs
        ' Exclude system tables:
        If Not T.Name Like "MSys*" And T.Name <> StTable Then
            RecCnt = T.RecordCount
            ' If it's linked table:
            If RecCnt = -1 Then RecCnt = DCount("*", T.Name)
            If RecCnt > 0 Then DB.Execute "INSERT INTO " & StTable & _
                " (tblName, tblRecords) " & _
                "VALUES ('" & T.Name & "', " & RecCnt & ")", dbFailOnError
        End If
    Next T

    Set RST = DB.OpenRecordset("SELECT * FROM " & StTable, dbOpenDynaset)
    If RST.RecordCount > 0 Then
        Do Until RST.EOF
            Debug.Print "Processing table " & RST("tblName") & "..."
            SizeBef = FileLen(NewDB)
            DB.Execute ("SELECT * " & _
            "INTO " & RST("tblName") & " IN '" & NewDB & "' " & _
            "FROM " & RST("tblName")), dbFailOnError
            SizeAft = FileLen(NewDB) - SizeBef
            RST.Edit
                RST("tblSize") = SizeAft
            RST.Update
            Debug.Print "    size = " & SizeAft
            RST.MoveNext
        Loop
    Else
        Debug.Print "No tables found!"
    End If
    RST.Close: Set RST = Nothing

    Debug.Print ">>> Done! <<<"
    MsgBox "Done!", vbInformation + vbSystemModal, "CheckTableSize"
    Kill NewDB
    Set DB = Nothing

    DoCmd.OpenTable StTable, acViewNormal, acReadOnly
End Sub

from my github repository

I am using Access 2003,it is easy to get the table record count. Table record count means size of table size. The more record, the bigger size. How to get table record count?

1. Open the database by Access 2003,
2. Click tab "Database Tools"
3. Click "Database Document Manage Tool"(May be some other name. )
4. Click "All", and "OK"
5. You will see a new white ground page shows table info of all table. Export to txt by click "TXT"(Under Word icon)
6. You will save the TXT to a file. Let's name it tableinfor.txt
7. Open "tableinfo.txt" by txt editor. search keywords"RecordCount". I think you know which table take the most space.

You could copy each table individually to separate Access databases, then compare the size of each. Although it wouldn't give you the exact size of the tables themselves, the size of each file shows approximately the size of each table.