[AccessD] Table size

  Below is some code that may come a bit closer, but even so field size
measures characters, not storage space.  Plus your not taking into account
variable length fields, any of the database overhead (page and DB), or
indexes associated with a table.

  So even at best, all this will be off by quite a bit.

Jim.

Public Sub showSizes()
Dim sql As String
Dim td As TableDef
Dim recordSize As Long
Dim numRecords As Long
Dim inMemos As Long
Dim CurrentDb As Database

Set CurrentDb = gTL1DB

                ' Create a temporary table for results:

                        ' delete the table first, in case it's still there
On Error Resume Next
CurrentDb.Execute "DROP TABLE T___tmp"
On Error GoTo 0
                
sql = "CREATE TABLE T___tmp(name TEXT (100), size INTEGER,"
sql = sql & " recordSize INTEGER, numRecords INTEGER, inMemos INTEGER)"
CurrentDb.Execute sql

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("T___tmp")
            
            ' for each table, add a record in T___tmp with its size info.

For Each td In CurrentDb.TableDefs
    recordSize = 0
    numRecords = 0
    inMemos = 0

    rs.AddNew
    rs!name = td.name
    rs!size = sizeEstimate(td.name, recordSize, numRecords, inMemos)
    rs!recordSize = recordSize
    rs!numRecords = numRecords
    rs!inMemos = inMemos
    rs.Update
    Next td
rs.Close

                    ' print table size info in decreasing order of size
Set rs = CurrentDb.OpenRecordset("SELECT * FROM T___tmp ORDER BY size DESC")
Do Until rs.EOF
    If rs!size > 0 Then
        Debug.Print rs!name; Tab(20); rs!size; Tab(30); " = "; rs!inMemos;
        Debug.Print Tab(45); "+ ("; rs!recordSize; Tab(55); " * ";
rs!numRecords; ")"
    Else
        Exit Do
        End If
    rs.MoveNext
    Loop
rs.Close

End Sub

Public Function sizeEstimate(tableName As String, recordSize As Long,
numRecords As Long, inMemos As Long) As Long
Dim rs As Recordset
Dim fld As field
Dim msg As Integer
Dim CurrentDb As Database
Dim colMemos As New Collection


Set CurrentDb = gTL1DB

On Error Resume Next
Set rs = CurrentDb.OpenRecordset(tableName)
If Err Then
    sizeEstimate = 0
    Exit Function
    End If
If rs.EOF Then
    sizeEstimate = 0
    Exit Function
    End If
rs.MoveLast
numRecords = rs.RecordCount
'Debug.Print
'Debug.Print tableName
                ' get the size of the fixed-size fields
For Each fld In rs.Fields
    recordSize = recordSize + fld.size
    If fld.Type = dbMemo Or fld.Type = dbLongBinary Then
        colMemos.Add fld.name
        End If
'    Debug.Print fld.name; " size "; fld.size
Next fld

sizeEstimate = (recordSize * numRecords) 'Bytes

Dim ff As Variant
                    ' add up all the memo and long binary fields in
                    ' all the records.
If colMemos.count > 0 Then
    rs.MoveFirst
    Do Until rs.EOF
        For Each ff In colMemos
            inMemos = inMemos + rs.Fields(ff).FieldSize
            Next ff
        rs.MoveNext
        Loop
    End If

sizeEstimate = sizeEstimate + inMemos

'If SizeEstimate > 1073741824 Then '1073741824 Bytes per GigaByte
'    msg = MsgBox("This view may still be larger than 1 GB.  Do you wish to
continue?", vbOKCancel + vbCritical, "View Size Warning")
'    If msg = 2 Then GoTo SizeEstimate_Exit
'End If

sizeEstimate_Exit:
rs.Close
End Function

 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Wednesday, September 30, 2009 6:13 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

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