[AccessD] Table size

Max Wanadoo max.wanadoo at gmail.com
Wed Sep 30 19:34:31 CDT 2009


Yes, but (from memory) what JC actually asked for was TABLE size.

Bit more problematical, eh?

Now, does he mean ACTUAL size, ie bytes taken or  DECLARED size?

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jack and Pat
Sent: 01 October 2009 01:30
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Table size

Max,

Totally agree on the need for compact/repair to get  meaningful info. There
could be all kinds of  junk from a variety of tests etc that would skew the
"size"  upwards.

Jack

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Wednesday, September 30, 2009 8:21 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Table size

Also, when I ran my version it came up with > 4Gb but disk space is 1.3 Gb
so counting the field Size is not very accurate because clearly MS does
sparse saving of actual data and not declared field sizes.  So, one option
would be 1) do you want maximum possible data size assuming a memo field of
64Kb, or 2) actual data stored working with len() function for memo fields
etc.

Loads of options and ways of looking.  Probably the best way of looking
overall at it is to compact/repair and take the disk size.  Even then it is
1) block size or 2) actual byte size..

Ooh, my head...

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: 01 October 2009 01:06
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Table size

ROTFL.  Don't worry about it Max.  If it was easy MS would hand it off to
the same summer interns 
that design North Wind DB and they would screw it up.

John W. Colby
www.ColbyConsulting.com


Max Wanadoo wrote:
> Sometime, you guys are just impossible...
> 
> Max
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: 30 September 2009 23:36
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [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.
> 
-- 
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

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




More information about the AccessD mailing list