VBS script for getting the list of fields in a MS Access database
This script will scan a MS Access database, loop through all tables, get the list of fields and properties and will generate a .csv file
Table of Contents
Get a raw copy of
access_get_fields_list.vbs and save it onto your disk.
If you want, get also a copy of
access_get_fields_list.cmd and save it in the same folder.
With the .vbs only
- Place the
access_get_fields_list.vbsscript in the folder where you've your database
- Start a DOS prompt
- Go to the folder where you've your database (and also the script)
cscript access_get_fields_list.vbs "my_db.accdb"
With the access_get_fields_list.cmd
- Save the .vbs and the .cmd files in the same folder (f.i.
c:\data\); the location isn't important.
- Edit the
access_get_fields_list.cmdfile in Notepad
- Modify the path to the database (in the example, it's
C:\temp\my_db.accdb, modify and type the path to your database).
- Save the file
- Just double-clic on the file to execute it.
Note : the generated file is a .csv file, stored in your
%TEMP% folder, the script will open it with MS Excel and add a few features like a title, autofilters, ... so, if you save the file, you'll obtain a .xlsx file.
Description of returned info
- 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 contains 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
ShortestSizewill be set to 4)
- LongestSize : When the table contains records, the
LongestSizeinfo is "what is the biggest information stored in that field ?" (example : if the field is a firstname, size 255 but the longest firstname is
LongestSizewill be set to 10)
- Position : The position of that field in the structure of the table (is the first defined field, the second, ...)
- Occurences : How many time, that specific
FieldNameis found in the entire database. If you've a lot of tables, perhaps the field called
CustomerIDis used in the
Customerstable and in the
Orderstable too so
Occurenceswill be set to 2 in this case.