Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 08-31-2019, 02:00 AM   #1
vkl
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 3 Times in 2 Posts
vkl is on a distinguished road
Table Size Analysis

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 by vkl; 08-31-2019 at 12:32 PM. Reason: spelling
vkl is offline  
The Following 2 Users Say Thank You to vkl For This Useful Post:
isladogs (08-31-2019), MrHans (08-31-2019)
Old 08-31-2019, 03:12 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,256
Thanks: 115
Thanked 3,076 Times in 2,794 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Table Size Analysis

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 08-31-2019 at 11:22 PM. Reason: Grammar
isladogs is offline  
The Following User Says Thank You to isladogs For This Useful Post:
vkl (08-31-2019)
Old 08-31-2019, 03:36 AM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Table Size Analysis

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.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline  
Old 08-31-2019, 09:23 AM   #4
vkl
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 3 Times in 2 Posts
vkl is on a distinguished road
Re: Table Size Analysis

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.
vkl is offline  
Old 08-31-2019, 11:11 AM   #5
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,256
Thanks: 115
Thanked 3,076 Times in 2,794 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Table Size Analysis

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 08-31-2019 at 09:36 PM.
isladogs is offline  
Old 08-31-2019, 10:00 PM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Table Size Analysis

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)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline  
Old 08-31-2019, 10:20 PM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,256
Thanks: 115
Thanked 3,076 Times in 2,794 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Table Size Analysis

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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 08-31-2019 at 11:32 PM.
isladogs is offline  
Old 09-01-2019, 02:25 AM   #8
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Table Size Analysis

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline  
Old 09-01-2019, 03:42 AM   #9
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,256
Thanks: 115
Thanked 3,076 Times in 2,794 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Table Size Analysis

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 09-01-2019 at 08:53 AM.
isladogs is offline  
Old 09-04-2019, 01:05 AM   #10
vkl
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 3 Times in 2 Posts
vkl is on a distinguished road
Re: Table Size Analysis

Quote:
Originally Posted by isladogs View Post
The OP can answer the points directed at him.
You are doing my job very well

Quote:
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.

vkl is offline  
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Data analysis in the Table pshem Modules & VBA 14 10-29-2018 12:08 PM
voyage leg analysis lakerdave General 3 12-05-2016 11:13 PM
Analysis Database matbow General 1 03-20-2006 12:30 AM
[SOLVED] table design analysis needed metrodub Tables 1 12-24-2004 04:45 AM
Analysis with Excel waxdart23 General 2 11-29-2002 04:10 AM




All times are GMT -8. The time now is 03:46 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World