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

how to find space used by tables in sql server
access database size limit
ms access size of objects
find table size in database
how to reduce access database size
how to get sql table size in gb
why is my access database so big
ms access how to get table size

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 used space.

Regards

/Frank

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?

How to determine the size of each table in Access database , Now my question is, how can I determine how much space does each table consume 3- Usually there should not be activity during the day, however most of the I use the following code to delete and reload the data to that access database 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.

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

Working with Large Access Databases, When someone mentions large databases in Microsoft Access, he is generally thinking about a database containing tables holding hundreds of You're certainly using more space to define the properties and methods of the object. Reports� There are several ways to pull the data to find out the space used for a table. One simple way to do this is to use sp_spaceused to get the space used for a table. We will use the AdventureWorks database for this test. Here is the simple command to get the data for one table.

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.

Determining space used for all tables in a SQL Server database, used for a table. We will use the AdventureWorks database for this test. is very useful! Now i can find out which table is using the most space with ease! up the most space. I also wrapped into a SPROC for easy access. Take a look at these related tips about free space Script to determine free space to support shrinking SQL Server database files; Collecting SQL Server database usage information for free space and trending; Determining space used for each table in a SQL Server database; Determine space used for a table using SQL Server Management Studio

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.

Access specifications - Access, The following list of tables applies to Access databases. Specific version 2 gigabytes, minus the space needed for system objects. Tip: For more information on reducing the size of your database, see Help prevent and correct database file problems by using Compact and Repair. You can create a database without using forms by simply editing your data in the table datasheets. However, most database users prefer to use forms for viewing, entering, and editing data in the tables. You can program command buttons to determine which data appears on the form, open other forms or reports, or perform a variety of other tasks.

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.

Move Access data to a SQL Server database by using the Upsizing , Over time, most database applications grow, become more complex, and need to The Upsizing Wizard works best when there is plenty of disk space available. For a linked table, Access uses the value in timestamp fields to determine� The table includes Database_id, data_space_id and File_id to uniquely identify each file and assit linking to other views (e.g., sys.databases) to obtain additional information. Accessing sys.master_files eliminates the need for dynamic SQL, using the undocumented stored procedure, creating a global temp table, etc.

Microsoft Access Tables, Primary Key Tips and Techniques, Optimally Defining Primary Keys in Microsoft Access Tables. Microsoft Access, SQL Server, Oracle, etc., let you create tables and specify any Text fields require more bytes than numeric fields, so using a number saves considerable space. Because Access data types differ from SQL Server data types, Access must determine the most appropriate Access data type to use for each column of each SQL Server table or view that you import or link to. For example, a SQL Server column of the data type bit is imported or linked into Access with the data type Yes/No.

Finding and Deleting Unused Objects and Code in Microsoft Access , It's very easy to create objects in a Microsoft Access database whether it's an MDB, It creates confusion when trying to figure out how all the database objects table driven systems, or interactive use of the objects, but it does capture most of� Hi, Could anyone guide me writing a sql query through which I can get the frequently used objects (tables, indexes, etc) especially tables. Say I have 10 tables and I would like to know which tables has the highest number of "select" statement on it.

3. Data, Tables, and Database Design, Identify the main topic headings for your data, and create tables for each distinct More to the point, what is a key, and what is a primary key, and why should I care? What Access doesn't tell you is that using spaces or apostrophes in names� Over time, most database applications grow, become more complex, and need to support more users. At some point in the life of your Microsoft Office Access application, you might want to consider upsizing to it to a Microsoft SQL Server database to optimize performance, scalability, availability, security, reliability, and recoverability.

Comments
  • Thanks for pointing out that tool. Unfortunately, the tool tells me only that most of the space is occupied by "Memo/OLE" data, but it does not tell me which table contains how much "Memo/OLE" data. Instead, all "Memo/OLE" data is grouped under a header "## not directly related to one table ##". Compressing the database does not help; the MDB file does not shrink at all. - Frank
  • As a workaround, I now have imported the MDB database to an SQL Server, and in SQL Server Management Studio, I have added the column "Data Space Used" to the list view. This tells me which table uses how much space. Using that procedure, I found out that a single table in my database almost uses all of the disk space.
  • Memo/OLE data is not actually stored in the record. Instead there is a pointer to a page where each Memo/OLE is actually stored. So it's not actually with the record. However there is some VBA code that you could run to determine which tables have Memo/OLE fields and how many of them there are. Hmm, and possibly the size of each but I'd have to poke about a bit on that. Although you now appear to have figured out which table is taking up the space.
  • Great work, thanks! I had to change the As Database in your variables to As Variant since access didn't want me to run the code for some reason(?), worked like a charm after. :)
  • I want to note that I was able to improve the export speed of the above code by 70% by using ADO with SELECT * INTO instead of DoCmd.TransferText after reading this: stackoverflow.com/a/14612302/1898524
  • + 1 for the "create a new database and export the table" suggestion rather than the shameless plug ;)
  • I do not think that will help: suppose you have two tables T1 and T2, each with one field of type Memo. T1 has 1 million records, and each memo field contains 1 character; T2 has 1,000 records, and each memo field contains 10,000 characters. Then, T2 clearly consumes more space than T1 (namely approximately 10 times as much). But the record count of T2 will be much less than T1.