# VBS - Retrieve the list of fields in a MS Access Database > Use a VBScript to easily retrieve a detailed list of fields from your MS Access database. Analyze field size, type, and actual value length to optimize your database structure. ---  **TL;DR** This article shares a VBS script that scans every table in an MS Access database and exports a detailed field report to Excel — name, type, defined size, and the actual shortest/longest value found in the data — making it easy to spot oversized fields (e.g. a 255-char field that never holds more than 10 characters) worth shrinking for optimization. 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: . 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 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](/blog/msaccess-optimize)): * 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](/blog/vba-access-export) gets your forms, queries and modules out as plain text files, ready to diff.