VBS - Retrieve the list of fields in a MS Access Database

Years ago, I created a .vbs script that scans a MS Access database, loops through each table, and, for each one, gets the list of fields.

For each field, a lot of information will be retrieved (non-exhaustive list): its 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 its size (f.i. 255 chars max) and will examine all records in the table to find, 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.

The report you'll get​

Double-click a .cmd file, wait a few seconds, and Excel opens by itself on this:

One line per field, for every table of the database, with the two columns that matter for optimization side by side: FieldSize (what the table allows) and LongestSize (what the data actually uses).

Understand the report​

• Filename: The MS Access filename (absolute)
• TableName: The name of the table
• FieldName: The name of the field found in that table
• FieldType: The data type (integer, string, date, ...)
• FieldSize: The maximum size defined in the table (f.i. 255 means that this field can contain up to 255 characters)
• ShortestSize: When the table contains records, the ShortestSize info is "what is the smaller information stored in that field?" (example: if the field is a firstname, size 255 but the shortest firstname is Paul, then ShortestSize will be set to 4)
• LongestSize: When the table contains records, the LongestSize info is "what is the biggest information stored in that field?" (example: if the field is a firstname, size 255 but the longest firstname is Christophe, then LongestSize will be set to 10)
• Position: The position of that field in the structure of the table (is the first defined field, the second, ...)
• Occurrences: How many times that specific FieldName is found in the entire database. If you've a lot of tables, perhaps the field called CustomerID is used in the Customers table and in the Orders table too so Occurrences will be set to 2 in this case.

How to reproduce it​

1. Copy/paste the source code of the two scripts (see below) and save them to text files (with Notepad). The first file to create will be access_get_fields_list.vbs, the second will be access_get_fields_list.cmd,
2. Before saving the access_get_fields_list.cmd be sure to edit the file and mention the full filename of your database,
3. You're ready: from your File Explorer, just double-click on access_get_fields_list.cmd. The analysis script will run and Excel will open at the end.

If everything goes fine, you'll see a DOS window and after a few seconds (depending on the size and complexity of the database), you'll have the report in Excel, automatically opened.

access_get_fields_list.vbs​

Get the source code from my GitHub page: https://github.com/cavo789/vbs_access_get_fields_list/blob/master/access_get_fields_list.vbs.

Copy the code in f.i. Notepad then save it on your disk, f.i. access_get_fields_list.vbs as filename.

access_get_fields_list.cmd​

Proceed the same way for the https://github.com/cavo789/vbs_access_get_fields_list/blob/master/access_get_fields_list.cmd script. Copy/paste the line in Notepad and remember to change the database name (no more C:\temp\my_db.accdb but yours).

Save the file onto your hard disk and give access_get_fields_list.cmd as filename. You can then quit Notepad.

What to do with the numbers​

In a context of optimization (see also How to optimize an existing MS Access database):

• Be sure not to use too large a field size. By default, MS Access suggests a size of 255 for text fields, but for a name or first name field, a size of 40 characters is enough.
• Check the LongestSize property: if you see f.i. a size of 4 this means that you're probably storing a code (a ZipCode f.i. is max 4 digits in Belgium). If the FieldSize is set to 50, you know you can reduce that size to 4.

Conclusion​

Two scripts, a double-click, and the structure of an inherited database is on screen as an Excel sheet you can sort. The gap between FieldSize and LongestSize is where the wasted space hides, and reading it takes a minute rather than opening thirty tables in design view.

Same "audit an existing database" spirit, different angle: Export MS Access objects gets your forms, queries and modules out as plain text files, ready to diff.