Table Size Analysis (1 Viewer)

vkl

New member
Local time
Today, 18:29
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

MVP / VIP
Local time
Today, 14:29
Joined
Jan 14, 2017
Messages
18,130
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, 22:29
Joined
May 7, 2009
Messages
18,987
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, 18:29
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

MVP / VIP
Local time
Today, 14:29
Joined
Jan 14, 2017
Messages
18,130
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, 22:29
Joined
May 7, 2009
Messages
18,987
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

MVP / VIP
Local time
Today, 14:29
Joined
Jan 14, 2017
Messages
18,130
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, 22:29
Joined
May 7, 2009
Messages
18,987
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

MVP / VIP
Local time
Today, 14:29
Joined
Jan 14, 2017
Messages
18,130
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, 18:29
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

MVP / VIP
Local time
Today, 14:29
Joined
Jan 14, 2017
Messages
18,130
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: 465

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:29
Joined
Feb 19, 2002
Messages
42,724
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?
 

isladogs

MVP / VIP
Local time
Today, 14:29
Joined
Jan 14, 2017
Messages
18,130
It is probably fair to say that the idea introduced in this thread by @vkl received a rather mixed reaction.

I made good use of this feature when working to reduce the size of a single very large BE table of UK postcodes from well over 1.6 GB to about 1.2 GB. I did this by tweaking datatypes & field sizes to make these as 'streamlined' as possible without losing any data.
The app was helpful to check the effects of each change

Although I previously made several changes to the code to fix a number of issues, there were several other valid criticisms including:
a) tables with complex fields (MVFs / attachments / column history) could not be handled (error 3838)
b) system tables were excluded

I have extensively rewritten the code to address those issues and more.
The attached app will now handle all local / linked & system tables including those with complex fields.
You can filter the types of table you wish to display
The table data is displayed on a form (or report):

1665579398236.png


I have posted this to a new article on my website with full code and the example app:

I hope that some of you find this useful / interesting
If there is sufficient interest, I may create an Add-In version of the app

If you still don't see the point, please feel free to ignore it!
 

Attachments

  • TableSizeCheck_v1.5.zip
    69 KB · Views: 184

isladogs

MVP / VIP
Local time
Today, 14:29
Joined
Jan 14, 2017
Messages
18,130
For info, I've just created a new version of this utility with additional functionality.

It is now a stand alone application which allows you to quickly analyze the tables (local/linked/system) in any Access database.
For each table, the table type, number of fields and records are displayed. You can also determine the size of each table, view its data and the table design. All field types can be handled including calculated and complex fields

I have been using this to analyse several databases for existing and potential clients

FormAllTables.png


The utility can be downloaded from
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:29
Joined
Feb 19, 2002
Messages
42,724
@Colin, I found a use for this app but not quite with the way it works. Looks like you delete the data on closing. Would you consider doing it the opposite way? That allows you do close the database but open it again and show the last database analyzed. Then clear everything when the browse button selects a new database.

I want to run this against a database at a client site but I don't want to review the data while I'm connected. I want to do it after the client emails me the results.

Otherwise nice job. Thanks
 

isladogs

MVP / VIP
Local time
Today, 14:29
Joined
Jan 14, 2017
Messages
18,130
@Pat Hartman
Thanks for your feedback
The app has been significantly enhanced since the free version was released. However, its now a commercial app.
The forthcoming version has many additional features including all object info / sizes / metadata, relationships, queries, dependencies, references & database properties

1688154493364.png


As not everyone will want all those features in each analysis. many features are optional:

1688154767078.png


For precisely the reason you mentioned, the new version will include the option to save the analysis data when the app is closed.

At some point, I may decide to add that feature to the earlier free & paid versions . . . but no promises at this stage
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:29
Joined
Feb 19, 2002
Messages
42,724
Thanks for the update. The paid version looks pretty comprehensive.
 

Users who are viewing this thread

Top Bottom