jwcolby
jwcolby at colbyconsulting.com
Thu Oct 1 14:49:20 CDT 2009
> Remember, some of us have got pubs to go to....can't spend the day coding...LOL
Precisely right!
John W. Colby
www.ColbyConsulting.com
Max Wanadoo wrote:
> 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.
>
>