Version 3.06 Approx 1.2 MB (zipped) First Published 1 Apr 2023 Last Updated 21 July 2023
Section Links:
Introduction
Using the Table Analyzer
Summary Reports
Table Viewer
Further Info
Version History
Purchase
Video
Feedback
| 1. Introduction | Return to Top |
This is a significantly enhanced version of my free Table Analyzer & Viewer utility. It is designed to analyze tables and their fields in external Access databases.
This application allows you to quickly analyze the tables in any Access database without opening it directly
It can be particularly 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 in planning any work required.
For example, tables with a large number of fields and/or complex datatypes may be a prime candidate for restructuring.
Similarly for tables which occupy a large disk space.
| 2. Using the Table Analyzer | Return to Top |
Click the Browse button on the form to select any Access database.
The database is scanned and a detailed list of its tables (local / linked / system) are obtained.
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
For encrypted databases, you will be prompted to enter the 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
For each table, the table type, number of fields and records are displayed.
For linked tables, the type of table (Access/SQL Server/SQL Azure/Sharepoint/Dataverse/Excel/Text etc) is also listed.
All tables containing complex fields are are identified
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)
Click the Get Table Sizes button to 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
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 7 seconds on a slow PC to transfer and measure 17 tables with total size approximately 0.35 MB
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 can be deleted as it is no longer needed.
However, you can click Cancel to save it so you can examine it. If so, you will see a message similar to this:
The table sizes are shown in kilobytes (kB). The structure of an empty table typically occupies 8 kB
A summary of the total number of tables checked and the total sizes in kilobytes (kB) 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.
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.
| 3. Summary Reports | Return to Top |
Click the Print Table Info button to view a summary of the table data as a report
Click the Print Field Info button to view a report giving detailed properties for each table field.
This includes the field datatype and size, primary keys and index information, and whether the field is required or zero length text strings are allowed.
You can also view a report of the fields for an individual table by clicking the Fields column on the form
| 4. Table Viewer | Return to Top |
Click the button next to the table name to view the table design.
A warning is displayed stating that the design of linked tables can NOT be modified
In this example, a wide variety of field datatypes were deliberately used for testing purposes.
You can also click the table name itself to view the table data.
NOTE:
a) The data in the selected table can be edited UNLESS it is a read only table
b) Newer datatypes such as Large Number (bigint) and Date/Time Extended can only be viewed using a version of Access that supports them e.g. 365.
It is NOT necessary to tick the related checkboxes in Access Options
| 5. Further Info | Return to Top |
a) 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.
b) 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
c) 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.
d) 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
e) 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.
f) 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
g) 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
h) There is a known issue when obtaining table sizes for databases containing several tables with complex fields.
Initially, only the first complex table size is calculated. A message similar to this will be shown:
To fix this, first click the Exclude All button to untick all Include checkboxes
Next click the Include checkbox for ONE table with Size = 0 then click Get Table sizes.
Repeat for each of the remaining tables in turn. The table sizes will then be correctly calculated
| 6. Version History | Return to Top |
v3.02 2023-04-01 Initial release
v3.04 2023-04-23 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
v3.06 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
| 7. Purchase | Return to Top |
The Professional version of the application is available to purchase from this website.
This application is intended for developers who would like full access to the source code together with the extra features described in this article.
Additional features may be added at a later date depending on user feedback.
On purchase, an ACCDB file will be supplied with full program code so you can further modify the application for your own purposes.
IMPORTANT: The code may not be republished or used in your own commercial applications without the explicit permission of Mendip Data Systems
Click to purchase the Pro version with: Full Source Code
Alternatively, if you do not need all the above features, you can use the FREE version which is supplied as an Access 'executable' file (ACCDE).
In the free version, program code is NOT available for viewing.
| 8. Video | Return to Top |
A video demonstrating the free version of this utility is available on my YouTube channel at https://youtu.be/BPO_xgvbxPY or you can click below:
| 9. 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 or if you have suggestions for additional features.
Colin Riddington Mendip Data Systems Last Updated 21 July 2023
|
Return to Example Databases Page
|
Return to Top
|