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

Select all