asked on

Determining Table Size in An access MDB, Quickly

Is there any quick way to determine how much space an individual table is taking up in an Access MDB.

For example: There are 20 tables (tbl01 thru tbl20) of varying definition and number of records in an Access MDB. The total MDB takes up 100MB. How much of the 100MB is used by tbl07?

I thought maybe properties would give you an idea but I don't see it. Breaking down the table into the size of each field, multiplying each field by the number of records and adding up all of the field totals is not what I mean by quick.

I realize that there may be no way to do this, just thought I'd throw it out there.

OldMuttonHead2

In the past, I have exported individual tables to a new database, then compact the new database and subtract the size of the blank database to get a rough estimate of how much a table takes up in terms of storage space. Not a very scientific way to do it, but it gives you a rough idea.

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlcktmguy

ASKER

Excellent Routine but when I first ran it, it gave me all sizes, except the tables. When I traced thru the logic I found your 'If' statement

If 1 = 2 Then

above the logic for the tables. Commenting that and the corresponding 'End If' made the table sizes generate.

Great solution, thanks.

IrogSinta

Sorry about that I was doing a quick test and I forgot to remove that.
For anyone else who happens upon this question,

here's the corrected version:

Private Sub GetSizes()
'*************************************************************************************************
' Purpose: Routine to get the sizes of tables, forms, reports and modules in the current database.
'        : I intentionally left out queries as their sizes would more than likely be insignificant.
'  Author: Ronald Gomez
'    Date: 16 Aug 2012
'   Usage: After running this routine, look at table ObjectSizes.
'*************************************************************************************************
    On Error GoTo CopyErr
    
    Dim i As Integer, k As Integer, intObjectType As Integer
    Dim strObject As String, strDbName As String, objName As String
    Dim clearArr As Variant
    Dim blankSize As Long
    Dim DOC As Document
    Dim cont As Container
    Dim wsp As Workspace
    Dim db2 As DATABASE
    Dim tdf As TableDef
    
    strDbName = "C:\ronTemp.mdb"
    clearArr = Array("Forms", "Reports", "Modules")  'doesn't work with Macros & queries
    
    If dcount("*", "MSysObjects", "MSysObjects.Name='ObjectSizes'") > 0 Then
        CurrentDb.TableDefs.DELETE ("ObjectSizes")
    End If
    
    CurrentDb.Execute "CREATE TABLE ObjectSizes (" _
        & "Name TEXT(255), " _
        & "Type TEXT(10), " _
        & "Size LONG)"

    
    ' Return reference to default workspace.
    Set wsp = DBEngine.Workspaces(0)
   
    'Get size of blank database
    If Dir(strDbName) <> "" Then Kill strDbName
    Set db2 = wsp.CreateDatabase(strDbName, dbLangGeneral)
    db2.CLOSE
    Set db2 = Nothing
    blankSize = FileLen(strDbName)
    Kill strDbName
    
    'Iterate to get sizes of objects (Forms, Reports, & Modules)
    For i = 0 To UBound(clearArr)
        For Each cont In CurrentDb.Containers
            If cont.NAME = clearArr(i) Then
                For Each DOC In cont.Documents
                
                    'create database
                    Set db2 = wsp.CreateDatabase(strDbName, dbLangGeneral)
                    strObject = clearArr(i)
                    objName = Replace(DOC.NAME, "'", "")
                    intObjectType = Switch(strObject = "Tables", 0, strObject = "Queries", 1, strObject = "Forms", 2, strObject = "Reports", 3, strObject = "Modules", 5)

                    DoCmd.TransferDatabase acExport, "Microsoft Access", strDbName, intObjectType, objName, objName
                    db2.CLOSE
                    Set db2 = Nothing

                    DoCmd.RunSQL "Insert Into ObjectSizes (Name, Type, Size) Values ('" & objName & "','" & strObject & "'," & FileLen(strDbName) - blankSize & ")"
                    Kill strDbName
                    
                Next DOC
            End If
        Next cont
    Next i
    
    'Iterate to get sizes of tables
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.NAME, 4) <> "MSys" And tdf.Attributes = 0 Then
            Set db2 = wsp.CreateDatabase(strDbName, dbLangGeneral)
            objName = Replace(tdf.NAME, "'", "")
            DoCmd.TransferDatabase acExport, "Microsoft Access", strDbName, 0, objName, objName
            db2.CLOSE
            Set db2 = Nothing

            DoCmd.RunSQL "Insert Into ObjectSizes (Name, Type, Size) Values ('" & objName & "','Tables'," & FileLen(strDbName) - blankSize & ")"
            Kill strDbName
        End If
    Next

    Msgbox "Done"
    Exit Sub
    
CopyErr:
    If err.Number = 3420 Then
        For k = 1 To 200: DoEvents: Next  'Pause needed if TransferDatabase was unsuccessful because of this error number; otherwise, database crashes database.
        Resume
    ElseIf err.Number = 2007 Then
        Msgbox "The object " & objName & " is still open", vbInformation + vbOKOnly, strObject
    Else
        Msgbox err.Number & vbCrLf & err.DESCRIPTION
    End If
End Sub

Open in new window