Is it possible to get the file size of access objects? (1 Viewer)

martinr

Registered User.
Local time
Tomorrow, 06:35
Joined
Nov 16, 2011
Messages
73
Is it possible (using the menu or VBA code) to find out the size of the
objects in an Access database file?
For example if the .accdb file is 300Mb, is there any way of comparing the size of the numerous tables/forms/reports/queries etc, that make up the database?
(I know that if I created a new access file and imported the objects from the working database that could give an indication but that would be quite a repetitive process).
 

martinr

Registered User.
Local time
Tomorrow, 06:35
Joined
Nov 16, 2011
Messages
73
Thanks namliam - i should have googled more!
I found the following code that returns the size of tables... would still be interested if there
is any way to find out the relative size of other objects (forms/reports)...
'---------------------------------------------------------------------------------------
' Procedure : ListAllTables_Size
' Author : Gustav(original)
' Created : 11/15/2009
' Purpose : To get approximate sizes of all
'non-MSys tables in an Access mdb.
'Outputs table names and sizes to immediate window.
'
'From AccessD discussion-
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: Microsoft DAO 3.6 Object Library
'------------------------------------------------------------------------------
'
Public Sub ListAllTables_Size()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Dim strName As String
Dim strFile As String
Dim strPath As String
Dim lngBase As Long
Dim lngSize As Long

On Error GoTo ListAllTables_Size_Error

Set dbs = CurrentDb
strName = dbs.Name
strPath = Left(strName, Len(strName) - Len(dir(strName)))

' Create empty database to measure the base file size.
strFile = strPath & "base" & ".mdt"
CreateDatabase strFile, dbLangGeneral
lngBase = FileLen(strFile)
Kill strFile
Debug.Print "Base size", lngBase

For Each tdf In dbs.TableDefs
strName = tdf.Name
' Apply some filtering - ignore System tables.
If Left(strName, 4) <> "MSys" Then
strFile = strPath & strName & ".mdt"
Debug.Print strName,;
CreateDatabase strFile, dbLangGeneral
DoCmd.TransferDatabase acExport, "Microsoft Access", strFile, acTable, strName, strName
lngSize = FileLen(strFile) - lngBase
Kill strFile
Debug.Print lngSize
End If
Next

Set tdf = Nothing
Set dbs = Nothing

On Error GoTo 0
Exit Sub

ListAllTables_Size_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ListAllTables_Size "

End Sub
 

namliam

The Mailman - AWF VIP
Local time
Today, 20:35
Joined
Aug 11, 2003
Messages
11,696
The size of your average form or query should be minimal, most size goes into the tables....

You can try replacing Tabledefs for Forms or QueryDefs in the code you got from the link.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:35
Joined
Sep 12, 2006
Messages
15,614
is this just academic, or is there an underlying reason for this being important?
 

Users who are viewing this thread

Top Bottom