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

The first step would be to connect to an external database. The article below explains how you can connect to an existing database:

• VBA, Open Access Database From Excel

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

• VBA Automation, External Access Database Create Table

Note: The examples in this article will be provided for 2 methods of automation:

1. Early binding
2. Late binding

In the latter method we add reference to the Access Object Library, before execution. It will run faster and we will have access to the VBA editor intellisense. On the other hand there is always the risk of compatibility issues arising when the program is run on a computer with a different version of Access installed. For more information about early vs late binding please see the link below:

• Microsoft MSDN: Early and Late Binding (Visual Basic)

Note: Although the code in this article was written in the Excel VBA editor, it can be used in any office application with VBA.

Adding Fields, To an External Access Database:

The codes below are based on the following assumptions:

1. The access database is located in the path “D:StuffBusinessTemp”.
2. The name of the access database is “NewDB.accdb”.
3. It is assumed the database has a table with the name “MyTable1”.

Late Binding:

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

Sub Example1()
'an Access object
Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
'add field
objAccess.CurrentProject.Connection.Execute ( _
"ALTER TABLE MyTable1 ADD COLUMN NewField 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.

Early Binding:

In early binding you will need to add reference to the Access Object Library before running the code. This can be done in the VBA editor (Tools>>Reference):

Note: I have the Microsoft Access 14.0 Object Library installed on my computer. There might be a different version installed the computer you are using. This will not affect the result of the code.

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

Sub Example2()
'an Access object
Dim objAccess As Access.Application
Set objAccess = New Access.Application
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
objAccess.CurrentProject.Connection.Execute ( _
"ALTER TABLE MyTable1 ADD COLUMN NewField 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.xlsm

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

See also:

• Get Field Names in External Access Database, VBA Automation
• Check If Table Exists in External Access Database, VBA Automation
• Get Table Names from External Access Database, VBA Automation
• Check If Field Exists in an External Access Database, VBA Automation

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