How to display table size?

Gkirkup

Registered User.
Local time
Today, 11:55
Joined
Mar 6, 2007
Messages
628
Is there a way to display the size of the tables? I am using Access 2010.

Robert
 
How many MB the table uses, within the database.

Robert
 
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.
 
Here is a routine that I have used with acc 2003.
It came from a group in which I participated.

Code:
'---------------------------------------------------------------------------------------
' 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
 

Users who are viewing this thread

Back
Top Bottom