Several years ago, I had to intervene on an MS Access database that had been created by a person of good will but with little affinity for computers and optimization.
The result: a cumbersome and exasperatingly slow database.
In this article, resurrected from my personal archive, I'm going to present the different stages I went through.
Years ago, I've created a .vbs script that will scan a MS Access database, loop for each table and, for each of them, will get the list of fields.
For each fields, a lot of information will be retrieved like, not exhaustive, his name, size, type, ... and also the shortest and longest value size (for text and memo fields). For instance, if a text field is found, the script will retrieve his size (f.i. 255 chars max) and will examine all records in the table for retrieving, for that field, the smallest size (f.i. 10) and the greatest one (f.i. 50). So, if the max size is 50 and the size has been set to 255, perhaps the MS Access developer can safely modify the max size from 255 to 50.