VBA code to check each MS Object object size (1 Viewer)

[cheer]

I would like to ask if the MS Access provide codes to check the size of each object such as Access table, linked table, linked query, Access query, form, table, macro, module and etc.

 

[isladogs]

Depends what you mean by size.
For example:

Number of records? Yes
Number of fields? Yes
Lines of code used? Yes
Disk space used? No

Etc

 

[cheer]

Depends what you mean by size.
For example:

Number of records? Yes
Number of fields? Yes
Lines of code used? Yes
Disk space used? No

Etc

TQ. How to do that ?

 

[isladogs]

This covers many of the possibilities

https://www.access-programmers.co.uk/forums/showpost.php?p=1553897&postcount=2

 

[isladogs]

Meant to ask before.
What did you mean by 'linked query'?
Query in a linked Access db or a SQL server view?
Or what?

 

[cheer]

Meant to ask before.
What did you mean by 'linked query'?
Query in a linked Access db or a SQL server view?
Or what?

Should be linked table. Not the linked query.

Besides the solution that you have provided on the above for the number of rows, anyone can suggest codes to check for each object size ?

 

[isladogs]

For info:
a) a linked query from another database is just treated as a query
b) a SQL server view is treated as a table

suggest codes to check for each object size ?

Once again, I'll ask what you mean by that.
If you mean the amount of disk space occupied by that database object, I've already answered your question.

 

[jdraw]

Just saw this.
I have a proc from a few years back that gets approximate table sizes in a database.
The original was written by a colleague on a different forum/group. I have made a few minor changes.
You could experiment by transferring other object types to get their approximate sizes. You could also direct output to a table or a file.

The overall approach is:
.create a new empty database (.mdt) and get its size. This is the base size of an empty database.
.for each table in current database create a new database (.mdt) and transfer the table to the new database, get the size of the database with the table, then delete the base size to get approx size of the table

It's an approximation, but I've seen no other code to do this.

'---------------------------------------------------------------------------------------
' Procedure : ListAllTables_Size
' Author    : Gustav(original)
' Created   : 11/15/2009
' Purpose   : To get approximate sizes of all
'non-MSys tables in an Access database
'Outputs table names and sizes  to immediate window.
'
'From AccessD discussion-
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: Microsoft DAO 3.6 Object Library
'------------------------------------------------------------------------------
'
Public Sub ListAllTables_Size()

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    Dim strName As String
    Dim strFile As String
    Dim strPath As String
    Dim lngBase As Long
    Dim lngSize As Long
    Dim lngTblCnt As Long


10    On Error GoTo ListAllTables_Size_Error

20    Set dbs = CurrentDb
30    strName = dbs.name
40    strPath = Left(strName, Len(strName) - Len(Dir(strName)))

    ' Create empty database  to measure the base file size.
50    strFile = strPath & "base" & ".mdt"
60    CreateDatabase strFile, dbLangGeneral
70    lngBase = FileLen(strFile)
80    Kill strFile
90    Debug.Print "Base size", lngBase / 1024 & " KB"    'report approx KB

100   For Each tdf In dbs.TableDefs
110     strName = tdf.name
        ' Apply some filtering - ignore System tables.
120     If Left(strName, 4) <> "MSys" Then
130         strFile = strPath & strName & ".mdt"
140         Debug.Print strName & Space(40 - Len(strName)), ;
            'create a new database and add 1 table
150         CreateDatabase strFile, dbLangGeneral
160         On Error Resume Next
            'transfer 1 table to the new database
170         DoCmd.TransferDatabase acExport, "Microsoft Access", strFile, acTable, strName, strName
            'get the size then remove the base size to get approx table size
180         lngSize = FileLen(strFile) - lngBase  '
190         lngTblCnt = lngTblCnt + 1
200         Kill strFile  'delete the temporary file
210         Debug.Print lngSize / 1024 & " KB"
220     End If
230   Next
240   Debug.Print vbCrLf & vbTab & "Tables processed:" & lngTblCnt
250   Set tdf = Nothing
260   Set dbs = Nothing

270   On Error GoTo 0
280   Exit Sub



ListAllTables_Size_Exit:
290   Exit Sub

ListAllTables_Size_Error:
300   MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ListAllTables_Size of Module GustavProcs"
310   Resume ListAllTables_Size_Exit

End Sub

Note: You can get size of current database using

'---------------------------------------------------------------------------------------
' Procedure : GetDbLen
' Author    : Jack
' Created   : 11/13/2009
' Purpose   : To get the size of the current db file.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub GetdbLen()
10    Debug.Print CurrentDb.name & "  " & FileLen(CurrentDb.name) / 1024 & " KB approx"

End Sub

 

[isladogs]

Hi Jack

OK, I'll take back my previous comment.
It could be done if you really wanted to ...
I suppose you could extend the idea to forms, reports etc

The problems are of course that
1. The size will change every time a record is added or removed
2. Compacting will also change the size
3. It's a total palaver .....

I once saw another sample db that seemed to do the same thing & even created a pretty chart to display the values.
When I checked the data had been hard-coded into a table!

A bit like the charts that are described in this post:
https://www.access-programmers.co.uk/forums/showpost.php?p=1554224&postcount=1

 

[jdraw]

Hi Colin,
I recalled having done this previously. I searched and found this.
I posted this on UA years back

Yes there are provisos, that why I said approx. I'm not sure why people need these sizes because they can change quickly --additions/deletions/compact etc.

I did a compact and repair of my database before running the proc.

 

[isladogs]

Hi Jack

Here's a variation on the same theme that I found at
https://stackoverflow.com/posts/44557992/edit

In this one, the tables are saved to text file & the text file size is approx equal to the table disk size.

Code attached to simplify importing into a new module.
Remove the .txt extension and import in the VBE
The only change I've made is to add the time taken

Takes slightly longer than the code you provided but saves having to do any subtraction ...!

Thinking about it, I have some old code that can export all database objects to text files. So the idea could in principle be extended to all objects.

You could also read the file sizes of the text files into a new table in your database ... and calculate that .... loop ad infinitum

However, as it can never be precise, I'm not going to spend the time doing so ...

 

[jdraw]

Agreed.
You could do a save to text in a new directory and get all objects and sizes with file explorer. But it's still a approximation. If the user understands the values will/could change, then this is a starting point.

 

[isladogs]

Speaking of exporting objects as text files, I've just remembered seeing a link to this fairly old but still useful post in a recent thread:
https://access-programmers.co.uk/forums/showpost.php?p=448509&postcount=1

 

[jdraw]

Yes, that post by ghudson has been referenced many time over the years.

 

[The_Doc_Man]

To evaluate the sizes of all Access objects, you run into a problem. Some of the objects aren't visible as such. Some of the objects have two or three different sizes depending on how you ask the question. The only REAL way to do it (and I don't claim to have even tried this, much less done it) is to reverse-engineer the database file - but to do so is to RADICALLY violate the End User Licensing Agreement (which is WHY I have not done this.)

If you have text fields in your database, you have two sizes already - the maximum allocation for all fields in the table and the actual size based on some fields being shorter than their maximum allowed sizes. For instance, a Text(255) will take up its descriptor, probably about 8 bytes, plus its data, which could be anywhere from 0 (empty string or null) to 255 bytes long. So which length did you mean? Actual or potential?

Then, of course, we have to consider that we cannot account for deleted records in any table since we can't see them. So that is a third table size... the size of all extant records PLUS all deleted-but-not-yet-reclaimed records.

Queries have no size unless they are open, but the querydefs DO have a size - the length of the SQL string. And because we don't have the right to reverse-engineer, it becomes questionable as to just how much we know about the querydef format.

Form objects are collections (controls) within collections (sections) within collections (tab pages) within collections (Forms). The structures associated with each element will vary, so there is some really complex enumeration going on, and for label controls (or any other object that has a caption), there is a variable-length string there, too. Reports have the same problems.

Macros are tough to get, but I recall that they are stored as action names.

Modules are organized into variable-length lines, up to 65535 lines per module. But the "gotcha" is that we don't see the compiled code. At best it is a binary large object internal to the DB, visible as a BLOB in the MSysObjects table but it is hard to associate the BLOB with a particular module. And if we can't see it, we can't size it.

So ... before you ask for the size of something, be sure to define your terms and recognize that you can't always get what you might want.

 

[cheer]

Speaking of exporting objects as text files, I've just remembered seeing a link to this fairly old but still useful post in a recent thread:
https://access-programmers.co.uk/forums/showpost.php?p=448509&postcount=1

TQ. I got what i am looking for !

 

[isladogs]

And just out of interest following the input from several users....
What EXACTLY were you after?