Max Wanadoo
max.wanadoo at gmail.com
Wed Sep 30 19:13:24 CDT 2009
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