Can you see the size of the tables inside the database...

[ToyFox]

I have a database which has links to back end DB2 tables that are used to do queries. Over time this db has become large due to tables being imported etc....is there a way to look at the table sizes so I can delete the largest ones...

 

[dhookom]

I don't believe there is any way to see the size of any object within an MDB. You can probably estimate by the average number of characters in fields times the number of records.

Typically you just compact the database whenever you have been importing or whatever.

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

Perhaps no direct (e.g. intrinsic function), but the number of records is avilable and the (approximate loength of a record is not that hard to calculate (sum of field lengths + length of length of each occurance of each field in each index). The simple multiplication is an approximate :Table Size).




MichaelRed

 

[jedraw]

Here is a procedure to get approximate sizes of Access non System tables. Outputs table names and sizes to the Immediate window.

'---------------------------------------------------------------------------------------
' 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 AccessD 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