Connecting to MS Access Database using VBA (ADODB)

VBA is able to connect to an Access database to retrieve data using Access SQL Queries. Here is a step-by-step tutorial on how to do it.

Firstly you have to add a reference in your VBA

1. Go to your Visual Basic Editor > Tools > Reference
   
2. Tick the reference "Microsoft ActiveX Data Objects 6.1 Library" (Depending on your Microsoft Office version, the '6.1' might be a bigger or smaller number. Find a reference similar to the example above)
   
3. Press the OK button

Now here is the code you can use to connect to the Access database (you can paste it in a newly created module; right click any sheet > Insert > Module):

Sub DatabaseWithVBA()
'Handle any errors such as query syntax or file not found errors and displays it
On Error GoTo errorhandler

Dim connection As ADODB.connection
Dim query As ADODB.Command
Dim queryResult As ADODB.Recordset
Dim AccessDatabasePath As String
Dim SQLquery As String

'Fill in these properties to connect to the Access Database
'You can either specify the path relatively to the document or type in the absolute path
AccessDatabasePath = "<<Location of your Access Database File>>"
SQLquery = "<<Your SQL Query>>"

'Connect to the Access database
Set connection = New ADODB.connection
connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessDatabasePath & ";"
connection.ConnectionTimeout = 30 'Close the connection if it takes more than 30 seconds
connection.Open
Set query = New ADODB.Command
query.ActiveConnection = connection

'Execute and save the results of the query into queryResult
query.CommandText = SQLquery
Set queryResult = query.Execute

'Go through the retrieved data and manipulate it accordingly
Do Until queryResult.EOF
'Manipulate your data here! (Example prints the value of a selected column)
MsgBox queryResult.Fields("<<Column Name>>")
'Move to the next row
queryResult.MoveNext
Loop

'Close connections
If connection.State <> adStateClosed Then
connection.Close
End If
If Not query Is Nothing Then
Set query = Nothing
End If
If Not connection Is Nothing Then
Set connection = Nothing
End If

errorhandler:
If Err.Number <> 0 Then
MsgBox "Error number: " & Err.Number & vbNewLine & Err.Description
End If
End Sub

The main things you need to edit are:

• The location of the database access path
• The query to run after connecting to the database
• Column Names

Hope this has helped you on improving your productivity with Microsoft Office Tools!