How to display table size?

[Gkirkup]

Is there a way to display the size of the tables? I am using Access 2010.

Robert

 

[Beetle]

What do you mean by size?

 

[Gkirkup]

How many MB the table uses, within the database.

Robert

 

[Galaxiom]

The only way I have seen this done is to copy the database, remove the table, compact and then compare the size of the two files.

A function to do it was posted somewhere online. Good luck finding it.

 

[jdraw]

Here is a routine that I have used with acc 2003.
It came from a group in which I participated.

'---------------------------------------------------------------------------------------
' Procedure : ListAllTables_Size
' Author    : Gustav(original)
' Created   : 11/15/2009
' Purpose   : To get approximate sizes of all
'non-MSys tables in an Access mdb.
'Outputs table names and sizes  to immediate window.
'
'From Access  D discussion-
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: Microsoft DAO 3.6 Object Library
'------------------------------------------------------------------------------
'
Public Sub ListAllTables_Size()

  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  
  Dim strName As String
  Dim strFile As String
  Dim strPath As String
  Dim lngBase As Long
  Dim lngSize As Long
    
   On Error GoTo ListAllTables_Size_Error

  Set dbs = CurrentDb
  strName = dbs.name
  strPath = Left(strName, Len(strName) - Len(dir(strName)))
  
  ' Create empty database  to measure the base file size.
  strFile = strPath & "base" & ".mdt"
  CreateDatabase strFile, dbLangGeneral
  lngBase = FileLen(strFile)
  Kill strFile
  Debug.Print "Base size", lngBase
  
  For Each tdf In dbs.TableDefs
    strName = tdf.name
    ' Apply some filtering - ignore System tables.
    If Left(strName, 4) <> "MSys" Then
      strFile = strPath & strName & ".mdt"
      Debug.Print strName, ;
      CreateDatabase strFile, dbLangGeneral
      DoCmd.TransferDatabase acExport, "Microsoft Access", strFile, acTable, strName, strName
      lngSize = FileLen(strFile) - lngBase
      Kill strFile
      Debug.Print lngSize
    End If
  Next
  
  Set tdf = Nothing
  Set dbs = Nothing

   On Error GoTo 0
   Exit Sub

ListAllTables_Size_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ListAllTables_Size "
  
End Sub

 

[Number11]

Here is a routine that I have used with acc 2003.
It came from a group in which I participated.

'---------------------------------------------------------------------------------------
' Procedure : ListAllTables_Size
' Author    : Gustav(original)
' Created   : 11/15/2009
' Purpose   : To get approximate sizes of all
'non-MSys tables in an Access mdb.
'Outputs table names and sizes  to immediate window.
'
'From Access  D discussion-
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: Microsoft DAO 3.6 Object Library
'------------------------------------------------------------------------------
'
Public Sub ListAllTables_Size()

  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
 
  Dim strName As String
  Dim strFile As String
  Dim strPath As String
  Dim lngBase As Long
  Dim lngSize As Long
   
   On Error GoTo ListAllTables_Size_Error

  Set dbs = CurrentDb
  strName = dbs.name
  strPath = Left(strName, Len(strName) - Len(dir(strName)))
 
  ' Create empty database  to measure the base file size.
  strFile = strPath & "base" & ".mdt"
  CreateDatabase strFile, dbLangGeneral
  lngBase = FileLen(strFile)
  Kill strFile
  Debug.Print "Base size", lngBase
 
  For Each tdf In dbs.TableDefs
    strName = tdf.name
    ' Apply some filtering - ignore System tables.
    If Left(strName, 4) <> "MSys" Then
      strFile = strPath & strName & ".mdt"
      Debug.Print strName, ;
      CreateDatabase strFile, dbLangGeneral
      DoCmd.TransferDatabase acExport, "Microsoft Access", strFile, acTable, strName, strName
      lngSize = FileLen(strFile) - lngBase
      Kill strFile
      Debug.Print lngSize
    End If
  Next
 
  Set tdf = Nothing
  Set dbs = Nothing

   On Error GoTo 0
   Exit Sub

ListAllTables_Size_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ListAllTables_Size "
 
End Sub

This looks like a solution to what i am looking for but where is the output stored in a table?

 

[Gasman]

This looks like a solution to what i am looking for but where is the output stored in a table?

You do that yourself?

 

[isladogs]

How many MB the table uses, within the database.

Robert

See my article and free example app:

Table Analyzer & Viewer

This utility allows you to quickly analyze the tables in any database. For each table, the table type, number of fields and records are displayed. You can also determine the size of each table, view its data and the table design. All field types can be handled including complex and calculated fields

www.isladogs.co.uk

 

[arnelgp]

It came from a group in which I participated.

that is a presumptive size.
when you add table to blank database, it create the table, yes.
but it also add record to MsysObjects and other system tables.

 

[The_Doc_Man]

When the DB contains all numeric items, the table itself can be sized by adding the length of each field and then counting records. When the table contains at least one ShortText field, the size of the text field in the FieldDef object is a MAXIMUM, not an actual size. Since we can't see the indexes so easily, if that table is indexed, again we have trouble knowing the actual size. What's worse is that we don't necessarily know how the text strings are being managed and therefore don't know exactly how much storage overhead is involved.

 

[gemma-the-husky]

Isn't it sufficient to count the records in each table?
Why is the table size critical?

 

[GPGeorge]

Although Colin's tool should give you the answer you are looking for, the question remains the one Dave asked, "Why does this matter?" I would add, what problem will you solve by identifying the size of each table in an accdb, whether it's a back end or a front end?

If the concern, for example, is that the overall size of the accdb is reaching a critical level, knowing the size of each table in it might seem useful on the surface. But what are you going to do about it? Again, speculating, is the idea that you would start moving larger tables into a second back end in that scenario?

Or are you simply trying to decide whether it is time to consider upgrading the back end to a server-based database such as MySQL or SQL Server?