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:

  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:

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”:

Late Binding:

Sub Example1()
'Access object
Dim appAccess As Object

'create new access object
Set appAccess = CreateObject("Access.Application")
'open the acces project
Call appAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
appAccess.Visible = True
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):
Microsoft Access Object Library

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()
'Access object
Dim appAccess As Access.Application

'create new access object
Set appAccess = New Access.Application
'open the acces project
Call appAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
appAccess.Visible = True
End Sub

Result:
Result


Running SQL Commands:

SQL statements can be run using the code below:

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

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”:

Sub Example3()
'Access object
Dim appAccess As Access.Application

'create new access object
Set appAccess = New Access.Application
'open the acces project
Call appAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
'create new table
appAccess.CurrentProject.Connection.Execute ( _
"Create Table NewTable")
End Sub

Result:
NewTable
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, Open Access Database From Excel”

  1. Will says:

    The code to open my Access database appears to work, however the Access window immediately closes. Any idea what is going on? I’m using MS Office Professional Plus 2010.

Leave a Reply

Your email address will not be published. Required fields are marked *