[AccessD] Table size

Good idea, but it won't give you realistic figures.

That will give you an exagerated size for text fields - ie 255 characters as opposed to the 
actual size of the stored strings plus the 4 bytes for the size info - and what about memo 
fields?

Plus much of the space used by a table may be the storage of its indexes, not just its data.


-- 
Stuart


On 30 Sep 2009 at 23:12, Max Wanadoo wrote:

> 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.
> 
> -- 
> John W. Colby
> www.ColbyConsulting.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