[AccessD] Table size

You could try something  like this (below).
Knowing how many records you deal with, I have made the variable DOUBLES

Not entirely sure it is accurate as I have only just written it and never
tested it in real life, but it may help.

Max

Sub sGetTableSizes()
    Dim dbs As DAO.Database, tbl As DAO.TableDef
    Dim sql As String, fld As Field, dblTotSize As Double
    Dim dblFldSize As Double, dblRecCount As Double, dblTableSize As Double
    Set dbs = CurrentDb
    For Each tbl In CurrentDb.TableDefs
      If Not Left(tbl.Name, 1) = "~" And Not Left(tbl.Name, 4) = "Msys" Then
        dblRecCount = DCount("*", tbl.Name)
        dblFldSize = 0
        For Each fld In tbl.Fields
            dblFldSize = dblFldSize + fld.Size
        Next fld
        dblTableSize = dblFldSize * dblRecCount
        Debug.Print tbl.Name, dblTableSize
      End If
      dblTotSize = dblTotSize + dblTableSize
    Next tbl
    MsgBox "Total Size: " & Format(dblTotSize, "##,##")
exithere:
    Set dbs = Nothing: Set tbl = Nothing
    Exit Sub
End Sub

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: 30 September 2009 20:21
To: Access Developers discussion and problem solving
Subject: [AccessD] Table size

Is there any direct way to obtain the total storage used for each table.  A
client is approaching 
the 2 gb limit and needs to split the BE.

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com