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

Before creating a table you will need to connect to an external database. You could either create a blank access database using the topic covered in the article below:

• VBA, Create New Access Database From Excel

Or you could connect to an existing database using the topic covered in the article below:

• VBA, Open Access Database From Excel

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

1. Early binding
2. Late binding

In the first 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.

Creating Table, In External Access Database:

The example codes assume there is an access database located in the path “D:StuffBusinessTemp” under the name “NewDB.accdb”. They will create a table with the name “MyTable”.

Late Binding:

Sub Example1()
'an Access object
Dim objAccess As Object

Set objAccess = CreateObject("Access.application")
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
'create table
objAccess.CurrentProject.Connection.Execute ( _
"Create Table NewTable")
End Sub


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.

Sub Example2()
'an Access object
Dim objAccess As Access.Application

Set objAccess = New Access.Application
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
'create table
objAccess.CurrentProject.Connection.Execute ( _
"Create Table NewTable")
End Sub


Result:

Note: Attempting to create a table that already exists will result in a runtime error. A good idea would be to check if the table exists before trying to create it. This has been covered in the article below:

• VBA Automation, Check If Table Exists in External Access Database

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

• Access Table.xlsm

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

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