Here we are going to see how to connect to a database in Access using Excel VBA. Separating data and interface is a common practice in application development as it has the following benefits:

• Data Sharing: multiple users can view and handle the same information. This is called concurrency a main property of databases!
• Improved performance: users store only the required data on their pc, therefore connection is faster and data availability increases.
• Data security: having data stored in centralized computer allows more control. Also, if there is any problem in the interface there is less probability of corrupting the database.
• Development Flexibility: new interface versions can be developed without disrupting access to data.

Connecting to an Access DB Using Excel VBA

1. Setting connection

a. Set path name

Dim PthName As String
PthName = ActiveWorkbook.Path ‘if DB and workbook are in same folder

b. Set database name

Dim DbName As String
DBName = “DatabaseFile.mdb”

c. Set connection to DB

MS Office ‘97-03
Dim Conn As New ADODB.Connection
Conn.Open “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & PthName & DbName & “;“

MS Office 2007 /2010
Dim Conn As New ADODB.Connection
Conn.Open “Provider=Microsoft.ACE.OLEDB.12.0; Data Source=” & PthName & DbName & “;”
First you have to add ActiveX data objects 2.8 in your project references (Tools menu -> references)

2. Execute SQL Command

a. Set string for SQL command

Dim strSQL as String
strSQL = “SELECT * FROM Students”

b.i. Execute create, update and delete commands

Conn.Execute strSQL

 b.ii. Execute query command

Dim RecSet As New ADODB.Recordset
RecSet.Open strSQL, Conn

 Example: insert new student record into Students table

Dim strSQLstrSQL = “INSERT INTO Students (StudentName, Program, AdmYear) ” & _ “VALUES (“”” & stgStrNa & “””,””” & StrPr & “””,””” & StrAY & “””); “Conn.Execute strSQL

3. Return Data (Queries)

Putting result set into an Array makes data processing easier

Dim ResultArray as Variant
ResultArray = RecSet.GetRows
TotalRows = Ubound(ResultArray, 2)

4. Close Connection

a. Close the recordset

RecSet.Close
RecSet = Nothing

b. Close the connection

Conn.Close
Conn = Nothing

Handling Connection Errors

If an SQL command is incorrect or connection can’t be established VBA will generate an error. If this happens we need to close connection and recordset.

On Error GoTo ErrorCloseConnection
‘
‘Connection commands here
‘

ErrorCloseConnection:

RecSet.Close
Set RecSet = Nothing

Conn.Close
Set Conn = Nothing