Version 2.34 Approx 0.86 MB (zipped) First Published 18 Mar 2023 Last Updated 21 July 2023
Section Links:
Introduction
Table Analyzer
Table Viewer
Downloads
Version History
Purchase Source Code
Video
Feedback
| 1. Introduction | Return to Top |
This is a significantly updated version of a utility designed to analyze tables in external databases.
The original idea for this application was loosely based on a thread published at Access World Forums by member vkl in Aug 2019 : Table Size Analysis
This utility allows you to quickly analyze the tables in any database without opening it directly. It can, for example, be very useful when taking over existing applications from clients where a detailed understanding of the structure and size of each table is an important first step.
Select a database and a list of its tables (local / linked / system) are shown.
For encrypted databases, you will be prompted to enter the password.
For each table, the table type, number of fields and records are displayed.
You can also determine the size of each table (disk space occupied), view its data and the table design.
All field types can be handled including calculated and complex fields (attachment, multivalued and long text column history)
The utility calculates the approximate size (disk space occupied) of all tables by exporting them to a temporary database and comparing the database size before and after export.
The table data is saved after which the temporary database is deleted as it is no longer needed.
The original code by vkl worked for many local and linked tables but had several important drawbacks:
a) System tables were excluded.
b) Tables with complex fields (multivalued, attachment and column history memo fields) could not be handled.
These tables caused error 3838 - Multi-valued fields are not allowed in SELECT INTO statements.
c) Tables with calculated fields could not be handled.
These tables caused error 3959 - Calculated columns are not allowed in SELECT INTO statements.
d) Table names with spaces or special characters caused error 3067.
As a result, the original code received a rather mixed reception from some forum members.
Since then, I have made significant changes to the original code to address all the above issues and further extend its functionality.
It now handles all local, linked and system tables including those with complex and calculated fields.
| 2. Table Analyzer | Return to Top |
When the app is first opened, it shows very little information
To use this app, click the Browse button and select any Access database
Alternatively, click the This DB button to view a list of tables in the current database
If the selected database is encrypted, you will be prompted to enter the database password
NOTE:
ACCDE files can only be analyzed in the same Access bitness that they were created. This is a limitation of Access itself (not my app)
Attempting to analyze a 64-bit ACCDE file in 32-bit Access (or vice versa) will result in an error message similar to this
Once a database has been selected, the app will scan the selected database and display a list of all database tables.
It will determine the table type, number of fields and records in each table.
Whilst the scan is taking place, you will see a message similar to this . . .
The scan should only take a few seconds, even if the database has many tables. When the scan is complete, the table info will look similar to this
Next click the Get Table Sizes button. The app will now determine the disk space occupied by each table
This will again take a few seconds to complete depending on the number of tables and their size. A progress message is displayed
The code used to create the progress bar is fully explained in my Progress Bar article elsewhere on this website.
Another message is displayed when the update has been completed
For databases with a small number of tables this is almost instantaneous. Large databases with many tables will take longer.
In this case, it took a total of 58 seconds on a slow PC to transfer and measure 52 tables with total size approximately 1.2 GB
A summary of the total number of tables checked and the total sizes in bytes and megabytes (MB) is shown at the bottom of the form.
The form can be filtered to display selected table types only. For example, only local tables.
Here the form has been filtered to display only system tables
You can also choose to exclude certain tables from the table size analysis by unticking the Include check box.
This can be useful to save time if you are only interested in a few of the tables
If tables have been added or deleted, click the Refresh Table List button to update the data
Then click the Get Table Sizes button to get the latest table sizes.
Click the Print button to view the data as a report
NOTE:
1. The calculated table sizes are a good guide to the disk space occupied by the table but this may not be exact for several reasons.
For example, data is normally added / edited to tables over a period of time which may result in unoccupied space being allocated to the table.
2. The structure of an empty table typically occupies 8192 bytes or 8 kilobytes. Similarly the space occupied by a linked table is normally 8 kilobytes
3. To obtain the table sizes, a copy of each standard table is exported to the temporary database using a make table query (SELECT . . . INTO . . .)
For linked tables, the quoted table size is the disk space occupied by the table in the backend database.
4. However, exporting using a make table query cannot be used for tables with complex or calculated fields.
Each of these are instead exported using a DoCmd.TransferDatabase acExport statement
5. When a table with complex fields is exported to the temporary database, the deep hidden attached table on which it depends is automatically
transferred as well (though it remains deep hidden). This means that the complex table is fully functional in the temporary database.
It also means that the calculated table size for complex tables is actually for both tables combined.
6. The temporary database may get very large if you have many local / linked tables with a large number of records.
In the example above the linked Postcodes table contained over 2.6 million records and more than 50 fields. The table size was approximately 1.21 GB
7. If the temporary database reaches the Access file size limit of 2 GB, the process will stop and the temporary database may no longer be usable.
You should take care to avoid this issue
| 3. Table Viewer | Return to Top |
The table data can be viewed by double clicking the table name on the table list subform
NOTE: The data in the selected table can be edited UNLESS it is a read only table
The table design can also be viewed by clicking the Design button on the left of the subform.
NOTE: A warning is displayed stating that the design of linked tables can NOT be modified
| 4. Downloads | Return to Top |
This application is available as a FREE ACCDE file for 32-bit & 64-bit Access (version 2010 or later):
Table Analyzer & Viewer v2.34 (32-bit) Approx 860 KB (ACCDE - zipped)
Table Analyzer & Viewer v2.34 (64-bit) Approx 870 KB (ACCDE - zipped)
Download the appropriate bitness of the Table Analyzer application and unblock it.
For more details, see my article:
Unblock downloaded files by removing the Mark of the Web
Unzip and save the ACCDE file to a trusted location.
NOTE:
Depending on your Access Trust Center settings, running it from an untrusted location MAY result in repeated security warnings as system tables are checked
| 5. Version History | Return to Top |
v2.30 2023-03-18 Initial release
v2.32 2023-04-01 Fixed issues with boolean fields for non-English language versions of Access
Many thanks to fellow Access developer, Angel Mazo, from Madrid for alerting me to the issue and testing my solution
v2.34 2023-07-21 Bug fix to delete lock file when clearing analysis. Improved error handling messages
Thanks also to Lorenzo Garuglieri for alerting me to the lock file bug and checking the fixed version
| 6. Purchase Source Code | Return to Top |
This application is supplied FREE as an Access 'executable' file (ACCDE). This means program code is NOT available for viewing.
A Professional version of the application can also be purchased at a reasonable price.
This is intended for developers who would like full access to the source code together with additional features.
An ACCDB file will then be supplied with full program code so you can further modify the application or use parts of it in your own applications.
The latest version (3.02) of the Table Analyzer & Viewer Pro application also includes additional features including detailed information about:
a) linked table types (Access/SQL Server/SQL Azure/Sharepoint/Dataverse/Excel/Text etc)
b) the table fields and their properties in the selected database.
The fields column can also be clicked to open a report listing all fields and their properties for a selected table in the external database.
Alternatively, click the Print Field Info button to get the list of fields and their properties for all tables in the selected database
Additional features may be added at a later date depending on user feedback.
Click here for more details about the Table Analyzer & Viewer Pro application.
Click here to purchase the Pro version with Full Source Code
| 7. Video | Return to Top |
A video demonstrating this utility is now available on my YouTube channel at https://youtu.be/BPO_xgvbxPY or you can click below:
| 8. Feedback | Return to Top |
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Also, do let me know if you find any bugs in the application.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 21 July 2023
|
Return to Example Databases Page
|
Return to Top
|