How to Use a VBA Macro to Add a Text Field to Every Access Table
Question details
The user needs to automate the process of adding a specific text field (e.g., EditAuditInfo) to all user tables in a Microsoft Access database using a VBA macro.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Modifying the schema of an entire database by appending a tracking or audit column to every user table at once.
- Observed behavior
- A DAO VBA script is required to loop through all tables in the TableDefs collection, filter out system tables starting with MSys, and alter the table structure to add the new text field.
Back up your Microsoft Access database file before running any bulk VBA macros to alter table structures, as schema changes cannot easily be undone.
Use a DAO VBA Macro to Alter TableDefs
Run a VBA loop over the TableDefs collection to execute an ALTER TABLE command on non-system tables.
This method uses Data Access Objects (DAO) to loop through every table in your database. It checks the table name to ensure it isn't a system table (which typically start with 'MSys') and then executes a SQL command to add the text field.
Open the VBA editor in Access by pressing Alt + F11. Go to Tools > References, scroll down, and check the box for 'Microsoft DAO 3.6 Object Library' (or 'Microsoft Office Access database engine Object Library' for newer versions) and click OK.
In the VBA editor, click Insert > Module to create a new blank script window where you will write your macro.
Declare a DAO.Database and a DAO.TableDef variable. Write a For Each loop to iterate through CurrentDb.TableDefs. Use an If statement checking Left(TableDef.Name, 4) <> "MSys" to skip built-in system tables.
Inside your loop, use CurrentDb.Execute "ALTER TABLE [" & TableDef.Name & "] ADD COLUMN EditAuditInfo TEXT(255);", dbFailOnError. This SQL command appends the new text field.
Run the macro by pressing F5. Press Ctrl + G to open the Immediate Window to review any Debug.Print logs you added, then check your tables in the Access navigation pane to verify the EditAuditInfo column was successfully added.

Consider WPS Office for Your Document and Spreadsheet Needs
While Microsoft Access is a powerful tool for complex relational databases, managing data exports, documentation, and reporting can often be streamlined with WPS Office. WPS Office is a free, lightweight, and comprehensive alternative to Microsoft Office that perfectly handles Word, Excel, and PowerPoint files with a highly familiar user interface.

Frequently Asked Questions
Why am I getting a 'User-defined type not defined' error when compiling the macro?
This error usually occurs because the required DAO reference is missing. In the VBA editor, go to Tools > References and ensure that 'Microsoft DAO 3.6 Object Library' or 'Microsoft Office Access database engine Object Library' is checked.
How do I skip specific user tables in my VBA loop?
You can add an additional condition inside your VBA loop. For example, use 'If TableDef.Name <> "TableToSkip" Then' to prevent the ALTER TABLE command from executing on that specific table.
Can I add different data types instead of a text field using this VBA macro?
Yes, you simply need to change the SQL statement in your CurrentDb.Execute command. For example, instead of 'TEXT(255)', use 'INTEGER', 'DATETIME', or 'YESNO' depending on the data type you want to add.
What happens if a table already has a column with the same name?
By default, the CurrentDb.Execute method will throw a runtime error if the column already exists. You can bypass this by implementing simple error handling using 'On Error Resume Next' before the execute line, allowing the loop to continue to the next table.




