VBA, Create New Access Database From Excel
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:
- Late Binding
- 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:
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
One thought on “VBA, Create New Access Database From Excel”