Table Size Analysis (1 Viewer)

Status
Not open for further replies.

vkl

New member
Local time
Today, 11:55
Joined
Aug 31, 2019
Messages
5
How it works
Collect all non-system tables in database.
Export each table to a temporary database and compare size before and after.
Show the table with the collected information and delete the temporary database.

Usage
Copy this Sub to a global module and run it with F5.
Don't forget to delete the temporary table (Const StTable).

Tested with MS Access 2010 and .mdb file.
If your file contains a table with multi-valued fields, you will get error 3838.

Code:
Sub CheckTableSize()
    ' Table Size Analysis
    Dim DB As DAO.Database, NewDB As String, T As DAO.TableDef, SizeAft As Long, _
        SizeBef As Long, RST As DAO.Recordset, F As Boolean, RecCnt As Long
    
    Const StTable As String = "_Tables"
    
    Set DB = CurrentDb
    
    NewDB = Left(DB.Name, InStrRev(DB.Name, "\")) & Replace(Str(Now), ":", "-") & " " & _
        Mid(DB.Name, InStrRev(DB.Name, "\") + 1, Len(DB.Name))
    Application.DBEngine.CreateDatabase NewDB, DB_LANG_GENERAL
    
    F = False
    For Each T In DB.TableDefs
        If T.Name = StTable Then
            F = True: Exit For
        End If
    Next T
    If F Then
        DB.Execute "DELETE FROM " & StTable, dbFailOnError
    Else
        DB.Execute "CREATE TABLE " & StTable & _
            " (tblName TEXT(255), tblRecords LONG, tblSize LONG);", dbFailOnError
    End If
    
    For Each T In DB.TableDefs
        ' Exclude system tables:
        If Not T.Name Like "MSys*" And T.Name <> StTable Then
            RecCnt = T.RecordCount
            ' If it's linked table:
            If RecCnt = -1 Then RecCnt = DCount("*", T.Name)
            If RecCnt > 0 Then DB.Execute "INSERT INTO " & StTable & _
                " (tblName, tblRecords) " & _
                "VALUES ('" & T.Name & "', " & RecCnt & ")", dbFailOnError
        End If
    Next T
    
    Set RST = DB.OpenRecordset("SELECT * FROM " & StTable, dbOpenDynaset)
    If RST.RecordCount > 0 Then
        Do Until RST.EOF
            Debug.Print "Processing table " & RST("tblName") & "..."
            SizeBef = FileLen(NewDB)
            DB.Execute ("SELECT * " & _
            "INTO " & RST("tblName") & " IN '" & NewDB & "' " & _
            "FROM " & RST("tblName")), dbFailOnError
            SizeAft = FileLen(NewDB) - SizeBef
            RST.Edit
                RST("tblSize") = SizeAft
            RST.Update
            Debug.Print "    size = " & SizeAft
            RST.MoveNext
        Loop
    Else
        Debug.Print "No tables found!"
    End If
    RST.Close: Set RST = Nothing
    
    Debug.Print ">>> Done! <<<"
    MsgBox "Done!", vbInformation + vbSystemModal, "CheckTableSize"
    Kill NewDB
    Set DB = Nothing
    
    DoCmd.OpenTable StTable, acViewNormal, acReadOnly
End Sub
 
Last edited:

isladogs

CID VIP
Local time
Today, 07:55
Joined
Jan 14, 2017
Messages
15,661
Hi
Welcome to AWF and many thanks for uploading the code to the repository.
For future info, this area is moderated. Please see the sticky thread above about reporting your own post.

I had to make one small change to get it to work for me.
Your code for the NewDB line caused error 3044 - not a valid path
I changed the line to
Code:
NewDB = Left(DB.Name, InStrRev(DB.Name, "\")) & Format(Now(), "yyyymmddhhnn") & "_" & Mid(DB.Name, InStrRev(DB.Name, "\") + 1)

With that change it worked fine and will I'm sure be interesting for other members.
Out of interest I tested a table with a single field and record. Its size was reported as 12288 bytes

EDIT
OOPS. I copied the wrong code previously. Now corrected. Thanks to Gasman for letting me know
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
14,982
what's the use? I don't get it.
you can take the oldDB's filelen().
compact and repair get the filelen().
same thing no code.
 

vkl

New member
Local time
Today, 11:55
Joined
Aug 31, 2019
Messages
5
I edited the post a little bit.
You will see an error message 3838 if you run it on Northwind.accdb (but not on Northwind.mdb).


arnelgp, with this sub you can estimate how much disk space your tables occupy.
 

isladogs

CID VIP
Local time
Today, 07:55
Joined
Jan 14, 2017
Messages
15,661
Error 3838 = Multi-valued fields are not allowed in SELECT INTO statements.

So I think you meant multi-valued fields rather than multiple fields.
It would be easy enough to handle this error and bypass the table.
However, yet another good reason to avoid MVFs
Of course, MDB files do not support the dreaded MVFs so that error can't occur.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
14,982
if I were to use that sub on db that has form, reports, modules, macros.
also I have tables prefix with Msys (you are excluding this on the sub).
will I get all the table size?
Code:
tablesize = FileLen(oldDb with forms,report, modules, macros, my Msys) - FileLen(Newdb)
 

isladogs

CID VIP
Local time
Today, 07:55
Joined
Jan 14, 2017
Messages
15,661
Arnel
The OP appears to be offline so I'll answer

Why don't you just try the code rather than ask the question?
The code loops through copying all non system tables into a new database and calculates the increase in size of the external dB after each table is added.
It is irrelevant what else is in your original database

Example output:
Code:
Processing table PupilData...
    size = 471040 bytes
Processing table School_Details...
    size = 909312 bytes
Processing table SEN_Stages...
    size = 12288 bytes
Processing table tblEthnicityCodes...
    size = 12288 bytes
Processing table tblSettings...
    size = 12288 bytes
>>> Done! <<<

You can modify the code to include system tables but will need to modify the code including renaming them in the external database to avoid a conflict with the external system tables

Also, as previously requested, please report your own posts to moderated areas.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
14,982
yes i know it is calculating the (supposedly) the table size.
but did he know that whenever you Add, delete any table, Access also, on the background, add and delete to its Msys tables so this one grow also. so the result is compromised.

I am not an expert with table structure or any internals of access, but he should begin with it, what is the Data Structure of an access table. how many bytes does each table header has. how many bytes does a particular Long or Short Text field type can holds without data.

using VBA alone is not enough. you need higher end like C++ or C# to get the SizeOf a table Data Structure.
 

isladogs

CID VIP
Local time
Today, 07:55
Joined
Jan 14, 2017
Messages
15,661
The OP can answer the points directed at him.

The output is just a snapshot of the approximate space taken up by each non-system table at that moment in time. As the OP stated it is an estimate.
The number of bytes occupied by each part of the table can also be used as a rough guide to table size but in terms of the total space that is irrelevant .
It will indeed change as records are added, edited, deleted … as will certain system tables depending on the actions taken.
However in my opinion that doesn't mean the results are compromised.
A new snapshot can be done whenever the user desires to get the latest values.

However useful the code may or may not be, if you don't like it (and you clearly don't), then don't use it.
 
Last edited:

vkl

New member
Local time
Today, 11:55
Joined
Aug 31, 2019
Messages
5
The OP can answer the points directed at him.
You are doing my job very well :)

The output is just a snapshot of the approximate space taken up by each non-system table
Since we calculate the size of the entire file, we also include various technical information, whatever it may be.
Yes, I call it a estimation because we can't calculate exactly because of the relations between tables, unicode compression and other reasons.
 

isladogs

CID VIP
Local time
Today, 07:55
Joined
Jan 14, 2017
Messages
15,661
This topic came up in a question at UtterAccess.com yesterday and I provided a link to this thread.

There was a follow up question by Peter Hibbs. As a result I have done some tweaks to the code to:
1. Fix error 3067 for table names containing spaces
2. Handle error 3838 for tables with MVF, attachment or column history memo fields
3. Indicate whether tables are local, linked Access or linked ODBC
4. Add a procedure timer

The output table will be similar to this
1638638793924.png


The procedure is very quick. Running it on the tables shown took less than a second
I also tested on a database with a very large Access BE of about 1.6 GB. The procedure took just under 40 seconds to complete

The modified code should be imported into a standard module
Rich (BB code):
Option Compare Database
Option Explicit

  ' Table Size Analysis
    'Original code by vkl at https://www.access-programmers.co.uk/forums/threads/table-size-analysis.306631/
    'Modified by Colin Riddington (Mendip Data Systems) - 04/12/2021

Sub CheckTableSize()

    Dim Db As DAO.Database, NewDB As String, T As DAO.TableDef, SizeAft As Long, _
        SizeBef As Long, rst As DAO.Recordset, F As Boolean, RecCnt As Long, _
        strSQL As String, strType As String, bytType As Byte, lngStart As Long, lngEnd As Long, blnErr As Boolean
   
    Const strTable As String = "_TableSize"
    Const strErrText As String = "Err 3838 - code cannot be used on tables with multivalue, attachment or column history memo fields"
   
    On Error GoTo Err_Handler
   
    Set Db = CurrentDb
   
   ' NewDB = Left(DB.Name, InStrRev(DB.Name, "\")) & Replace(Str(Now), ":", "-") & " " & _
        Mid(DB.Name, InStrRev(DB.Name, "\") + 1, Len(DB.Name)) 'vkl
       
    NewDB = Left(Db.Name, InStrRev(Db.Name, "\")) & Format(Now(), "yyyymmddhhnnss") & "_" & _
        Mid(Db.Name, InStrRev(Db.Name, "\") + 1) 'Modified by CR to add exact time in file name
    Application.DBEngine.CreateDatabase NewDB, DB_LANG_GENERAL
   
    lngStart = Timer
   
    F = False
    For Each T In Db.TableDefs
        If T.Name = strTable Then
            F = True: Exit For
        End If
    Next T
   
    If F Then
        Db.Execute "DELETE FROM " & strTable, dbFailOnError
    Else
        Db.Execute "CREATE TABLE " & strTable & _
            " (tblName TEXT(255), tblType TEXT(20), tblRecords LONG, tblSize LONG, ErrorNote TEXT (255));", dbFailOnError
    End If
   
    For Each T In Db.TableDefs
        'CR - Exclude system tables, deleted tables (~TMPCLP*) and the output table strTable
        If Not T.Name Like "MSys*" And T.Name <> strTable And Not T.Name Like "~TMPCLP*" Then
           
            'get table type
            bytType = DLookup("Type", "MSysObjects", "Name = '" & T.Name & "'")
           
            Select Case bytType
           
            Case 1
                strType = "Local"
               
            Case 4
                strType = "Linked ODBC"
               
            Case 6
                strType = "Linked Access"
               
            End Select
           
           'get record count
            RecCnt = T.RecordCount
            'If a linked table, RecCnt = -1
            If RecCnt = -1 Then RecCnt = DCount("*", T.Name)
             
            If RecCnt > 0 Then Db.Execute "INSERT INTO " & strTable & _
                " (tblName, tblType, tblRecords) " & _
                "VALUES ('" & T.Name & "', '" & strType & "', " & RecCnt & ")", dbFailOnError
        End If
    Next T
   
    Set rst = Db.OpenRecordset("SELECT * FROM " & strTable, dbOpenDynaset)
    With rst
        If .RecordCount > 0 Then
            Do Until rst.EOF
                Debug.Print "Processing table " & !tblName & "..."
                SizeBef = FileLen(NewDB)
              ' DB.Execute ("SELECT * " & _
                    "INTO " & RST("tblName") & " IN '" & NewDB & "' " & _
                    "FROM " & RST("tblName")), dbFailOnError 'vkl - fails if table name contains spaces
               
                Db.Execute ("SELECT * " & _
                    "INTO [" & rst("tblName") & "] IN '" & NewDB & "' " & _
                    "FROM [" & rst("tblName") & "]"), dbFailOnError 'Modified by CR to handle spaces
               
                SizeAft = FileLen(NewDB) - SizeBef 'estimated table size
                .Edit
                    !tblSize = SizeAft
                .Update
                Debug.Print "Size = " & SizeAft & " bytes"
               
                'populate ErrorNote field if err 3838 is triggered
                If blnErr = True Then
                    strSQL = "UPDATE " & strTable & " SET ErrorNote = '" & strErrText & "' WHERE tblName='" & !tblName & "';"
                    Db.Execute strSQL, dbFailOnError
                    blnErr = False
                End If
               
                .MoveNext
            Loop
        Else
            Debug.Print "No tables found!"
        End If
        .Close
    End With
       
    Set rst = Nothing
   
    lngEnd = Timer
   
    Debug.Print "Table size check completed!"
    Debug.Print "Time taken = " & lngEnd - lngStart & " seconds"
   
    MsgBox "Table size check completed!" & vbCrLf & _
        "Time taken = " & lngEnd - lngStart & " seconds", vbInformation, "CheckTableSize"
   
    'delete temporary database
    Kill NewDB
    Set Db = Nothing
   
    'open temp table
    DoCmd.OpenTable strTable, acViewNormal, acReadOnly
   
Exit_Handler:
    Exit Sub
   
Err_Handler:
    'err 3067 occurred if table name contains spaces - no longer an issue
    If Err = 3067 Then Resume Next
   
   'error 3838 if MVF, attachment or column history memo fields used
    If Err = 3838 Then
        blnErr = True
        Debug.Print strErrText
        Resume Next
    Else
        MsgBox "Error " & Err & " in CheckTableSize procedure : " & Err.Description
        Resume Exit_Handler
    End If
End Sub

Alternatively you can easily import the code into your own databases after changing the .txt suffix to .bas
Hope that is useful to some of you. Thanks to @vkl for providing the original code
 

Attachments

  • modTableSize.txt
    5.2 KB · Views: 63

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:55
Joined
Feb 19, 2002
Messages
34,345
Since the BE should only contain tables anyway, I also don't know what use this code might be, especially since it does not work for certain conditions. What am I missing?
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom