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:

• Excel Connect To External Data (Access Database )

In order to create a blank access database from excel using VBA, please see the article below:

• VBA, Create New Access Database From Excel

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:

• Microsoft MSDN: Early and Late Binding (Visual Basic)

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

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:

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:

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

• Open 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