[AccessD] Table size

Max Wanadoo max.wanadoo at gmail.com
Thu Oct 1 13:10:04 CDT 2009


Gustav,

Remember, some of us have got pubs to go to....can't spend the day
coding...LOL

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 01 October 2009 17:37
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Table size

Hi Arthur

So much talking, so little action ...

<code>
Public Sub ListTableSize()

  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  
  Dim strName As String
  Dim strFile As String
  Dim strPath As String
    
  Set dbs = CurrentDb
  strName = dbs.Name
  strPath = Left(strName, Len(strName) - Len(Dir(strName)))
  
  For Each tdf In dbs.TableDefs
    strName = tdf.Name
    ' Apply some filtering.
    If Left(strName, 4) = "tblS" Then
      strFile = strPath & strName & ".mdt"
      Debug.Print strName, ;
      CreateDatabase strFile, dbLangGeneral
      DoCmd.TransferDatabase acExport, "Microsoft Access", strFile, acTable,
strName, strName
      Debug.Print FileLen(strFile)
      Kill strFile
    End If
  Next
  
  Set tdf = Nothing
  Set dbs = Nothing
  
End Sub
</code>

>>> jwcolby at colbyconsulting.com 01-10-2009 14:53 >>>
Jim,

 >   In fact I'm left wondering why someone hasn't done that already...

Because we all work for a living and no one wants to pay us to develop that?

;)

But you are correct, it wouldn't be that tough to do.

John W. Colby
www.ColbyConsulting.com 


Jim Dettman wrote:
> Max,
> 
> <<Oh no, I didn't mean it that way. What I  meant was the guys (and gals)
on
> this site are amazing with their knowledge and skills.  Impossible to keep
> up with.  I think I have done something "clever" and the say "yeah, watch
> this bud".>>
>  
>   Been there many times.  Sometime I feel like I live in the stone age
when
> I see a lot of the stuff on EE.
> 
> <<Hope I didn't offend anybody.  Emails sometimes can be difficult.>>
> 
>   Not here and I hope the reverse is true as well.  I really was just
trying
> to point out that it's a thornier problem then it first appears.   To
really
> do it right, you'd need to scan every page in the DB assigned to the table
> or do a compact and then just add up all the pages assigned to the table.
> And while the data page layouts for Jet 3.0 and 4.0 are pretty well known,
> what's known about the index pages and page usage maps are fuzzy at best.
> 
>   And then there are details such as page padding (leaving free space for
> new data); does JET do it or not?  So even scanning all the pages and
> getting a very accurate storage count of the data still might not tell you
> how much space a table would occupy.  If the fill factor was 50%, you
could
> be off by quite a bit.   The best approach I guess would be a compact,
then
> just count the pages assigned to the tables and indexes.
> 
>   Without going through all that, the only realistic approach I've found
is
> to import a single table into a fresh MDB and then check the size.  That
is
> accurate, but it's a lot of manual work.   And now that I say that, I
think
> between all of us we could come up with some code to do that automatically
> fairly quickly for a given DB.   In fact I'll see if I can squeeze out
some
> time latter today to do that.  Would not be all that hard; read the
> tabledefs, create a scratch DB, transfer the table, get the on disk size
of
> the scratch DB, save info into a table, delete scratch DB, and repeat till
> done.
> 
>   In fact I'm left wondering why someone hasn't done that already...
> 
> Jim.
> 
>  
> 
> -----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:13 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Table size
> 
> Oh no, I didn't mean it that way. What I  meant was the guys (and gals) on
> this site are amazing with their knowledge and skills.  Impossible to keep
> up with.  I think I have done something "clever" and the say "yeah, watch
> this bud".
> 
> Hope I didn't offend anybody.  Emails sometimes can be difficult.
> 
> Anyway, John.  Hope you got something out of it all - LOL.
> 
> 
> But, hey, tell you what, this might develop into a useful item.  I never
> even considered Indexes.  What about Reference overheads.  Tons of stuff.
> 
> 
> 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




More information about the AccessD mailing list