In this article I will explain how you can create a new (blank) access database from Excel. There are 2 methods for getting this done:

1. Late Binding
2. Early Binding

In early binding we add a reference to the Access Object Library. The benefit of using early binding is that our code will run faster and we will have access to intellisense. The downside is that a compatibility issue might arise when using the program on another computer with a different version of Access installed. Using late binding we let the compiler detect which version of Access is installed at runtime.

Note: Although this code was written in VBA for Excel, it is applicable to all other office applications.

Create Blank Access Database:

Late Binding:

Using the code below a blank access database will be created in the path “D:StuffBusinessTemp” under the name “NewDB”:

Sub Example1()
'the path to create the new access database
Dim strPath As String
'an Access object
Dim objAccess As Object

strPath = "D:StuffBusinessTempNewDB"
Set objAccess = CreateObject("Access.Application")
Call objAccess.NewCurrentDatabase(strPath)
objAccess.Quit
End Sub


Result:

Early Binding:
In order to use early binding we will need to create reference to the Microsoft Access Object Library. 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.

Similar to the code explained above the code below will create a blank Access database in the path “D:StuffBusinessTemp” under the name “NewDB”:

Sub Example2()
'the path to create the new access database
Dim strPath As String
'an Access object
Dim objAccess As Access.Application

strPath = "D:StuffBusinessTempNewDB"
Set objAccess = New Access.Application
Call objAccess.NewCurrentDatabase(strPath)
objAccess.Quit
End Sub


Running SQL Commands:

SQL statements can be run using the code below:

'SQL Statement
objAccess.CurrentProject.Connection.Execute (strSQL)


Where strSQL is a string with the SQL statement.

Example 1, Creating a New Table:

The code below create a new access database in the path “D:StuffBusinessTemp” under the name “NewDB.accdb” and create a new table called “NewTable”:

Sub Example3()
'the path to create the new access database
Dim strPath As String
'an Access object
Dim objAccess As Access.Application

strPath = "D:StuffBusinessTempNewDB"
Set objAccess = New Access.Application
Call objAccess.NewCurrentDatabase(strPath)
'create table
objAccess.CurrentProject.Connection.Execute ( _
"Create Table NewTable")
End Sub


Result:

You can download the file and code related to this article from the link below:

• Automate Access.xlsm

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