Jim Dettman
jimdettman at verizon.net
Thu Oct 1 12:16:45 CDT 2009
That needs to be modified a tad; a blank DB needs to be created and the
disk size needs to be subtracted after the table is exported into it.
It would also be nice to have the tables sizes stored in a table.
Jim.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, October 01, 2009 12:37 PM
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