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:
Using the code below a blank access database will be created in the path “D:StuffBusinessTemp” under the name “NewDB”:
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”:
Running SQL Commands:
SQL statements can be run using the code below:
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”: