VBA & Macro Problems

How to Use a VBA Macro to Add a Text Field to Every Access Table

Anum MushtaqAnum Mushtaq Oct 29, 2026 869 views

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.

How to Use a VBA Macro to Add a Text Field to Every Access Table
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.
Before you start

Back up your Microsoft Access database file before running any bulk VBA macros to alter table structures, as schema changes cannot easily be undone.

Solution 1Recommended

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.

1
Enable the DAO Object Library Reference

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.

2
Create a New Module

In the VBA editor, click Insert > Module to create a new blank script window where you will write your macro.

3
Write the Loop Script

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.

4
Execute the ALTER TABLE Statement

Inside your loop, use CurrentDb.Execute "ALTER TABLE [" & TableDef.Name & "] ADD COLUMN EditAuditInfo TEXT(255);", dbFailOnError. This SQL command appends the new text field.

5
Run and Verify

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.

Use a DAO VBA Macro to Alter TableDefs
Tip: Handling Duplicate Fields: Include an 'On Error Resume Next' statement right before the execute command, and 'On Error GoTo 0' immediately after it, to prevent the macro from crashing if the text field already exists in a specific table.
Free Microsoft Office alternative

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.

Seamless compatibility with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint formats.Free, lightweight, and fast-loading office suite.Built-in PDF editor to manage and convert database reports easily.Familiar user interface requiring zero learning curve for easy migration.
microsoft office alternative - wps office

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.