VBA, Open Access Database From Excel
In this article I will explain how you can open an existing Access database from Excel using VBA.
If you wish to only import the data into excel you could use the topic covered in the article below:
In order to create a blank access database from excel using VBA, please see the article below:
Similar to the what was mentioned in the article VBA, Create New Access Database From Excel there are 2 method for automating an Access database:
- Early binding
- 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:
Note: Although this code was written in VBA for Excel, it is applicable to all other office applications.
Open Existing Access Database:
The codes below will open the access database located in the path “D:StuffBusinessTemp” under the name “NewDB.accdb”:
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.
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 will open the access database located in the path “D:StuffBusinessTemp” under the name “NewDB.accdb” and create a new table called “NewTable”: