In this article I will explain how you can add fields to a table in an access database using VBA.

If the database does not contain any tables, you can use the topic covered in the article below to create tables for it:

• VBA Access Create Table

Adding Fields:

It will be assumed that the access database has a table with the name “MyTable1”.

Note: In order to work with the Recordset object you may need to add reference to the Microsoft ActiveX Data Object Library. This has been explained in the article below:

• VBA Access Recordset, Object Library

The code below adds a field to the table “MyTable1” with the name “NewField” of the “CHAR” type using Docmd.RunSQL:

Sub Example()
DoCmd.RunSQL ( _
"ALTER TABLE MyTable1 ADD COLUMN NewFiel2d CHAR")
End Sub


For more information about the different fields types please see the link below:

• Microsoft MSDN: Comparison of Data Types [Access 2003 VBA Language Reference]

Note: Attempting to add a field that already exists will result in a runtime error. A good idea would be to check if  the field exists before attempting to add it.

Result:

You can download the file and code used in this article from the link below:

• Add Field.Zip

Note: Although the code was written in the Excel VBA Editor, it can be written in any other office application.

See also:

• Access VBA Get Field Names
• Check If Table Exists Access VBA
• VBA Access  Get Table Names
• Check If Field Exists VBA Access

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com