Table Size Analysis (1 Viewer)

Status
Not open for further replies.

vkl

New member
Local time
Tomorrow, 01:42
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 Moderator
Staff member
Local time
Today, 22:42
Joined
Jan 14, 2017
Messages
13,178
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

error reading drive A:
Local time
Tomorrow, 05:42
Joined
May 7, 2009
Messages
9,310
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
Tomorrow, 01:42
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 Moderator
Staff member
Local time
Today, 22:42
Joined
Jan 14, 2017
Messages
13,178
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

error reading drive A:
Local time
Tomorrow, 05:42
Joined
May 7, 2009
Messages
9,310
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 Moderator
Staff member
Local time
Today, 22:42
Joined
Jan 14, 2017
Messages
13,178
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

error reading drive A:
Local time
Tomorrow, 05:42
Joined
May 7, 2009
Messages
9,310
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 Moderator
Staff member
Local time
Today, 22:42
Joined
Jan 14, 2017
Messages
13,178
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
Tomorrow, 01:42
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.
 
Status
Not open for further replies.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom