Part of the free Module 12: Excel VBA Course · Lesson 18 of 18 · Full Excel course
An ADODB connection lets Excel VBA talk directly to a database such as Microsoft Access, SQL Server or MySQL, running SQL queries and reading the results into a worksheet. In this lesson you will learn how to add the ActiveX Data Objects reference, build a connection string, open a Recordset, choose the right cursor and lock types, count and copy records, and close everything cleanly.
What ADODB is and when to use it
ADO (ActiveX Data Objects) is the Windows data-access library that VBA uses to reach any database with an OLE DB or ODBC driver. Instead of importing a whole table and filtering in Excel, you send a SQL statement and receive only the rows you asked for. This is how multi-user Excel front ends store data in an Access or SQL Server back end, how dashboards refresh from a company database, and how data-entry forms save records without the sheet ever holding the full table. ADO can also treat a closed Excel workbook or a CSV file as a database.
Add the ADO reference
- Press Alt+F11 to open the Visual Basic Editor.
- Choose Tools > References.
- Tick Microsoft ActiveX Data Objects 6.1 Library (2.8 also works on older systems) and click OK.
With the reference set you can declare the two objects you need, with IntelliSense support.
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.RecordsetThe Connection represents the link to the database; the Recordset holds the rows returned by a query. If you prefer no reference (late binding), declare them As Object and create them with CreateObject("ADODB.Connection").
Open the connection
The connection string names the provider and the data source. For an Access database:
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database.accdb"ACE.OLEDB.12.0 is the Access Database Engine; the same provider reads Excel files with Extended Properties="Excel 12.0;HDR=Yes". For SQL Server use "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DbName;Integrated Security=SSPI".
Open a Recordset
rst.Open Source:="SELECT * FROM TBL_Customer", ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, LockType:=adLockOptimisticSource is the SQL, ActiveConnection is the open connection, and the two remaining arguments control how the rows can be navigated and edited.
Cursor types
- adOpenForwardOnly (default): fastest and lightest; you can only MoveNext, and RecordCount returns -1.
- adOpenStatic: a snapshot; all Move methods work and RecordCount is reliable; other users’ changes are not visible.
- adOpenKeyset: all Move methods; edits and deletions by other users are visible, new records are not.
- adOpenDynamic: all Move methods; every change by other users is visible; heaviest on the server.
Lock types
- adLockReadOnly (default): read only; no locks on the table.
- adLockOptimistic: you can add, edit and delete; the record is locked only at the moment of Update, so two users editing the same row produce an error for the second one.
- adLockPessimistic: the record is locked as soon as you start editing it, blocking other users until Update.
- adLockBatchOptimistic: several changes are sent together with UpdateBatch.
For reporting use adOpenStatic with adLockReadOnly; for data-entry forms use adOpenKeyset with adLockOptimistic.
Complete example: count and copy records to a sheet
Sub Get_Customer_Records()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sh As Worksheet
Dim qry As String, i As Long
On Error GoTo ErrHandler
Set sh = ThisWorkbook.Sheets("Customers")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
ThisWorkbook.Path & "\Database\Database.accdb"
qry = "SELECT CustomerID, CustomerName, City FROM Customers WHERE City = 'Delhi' ORDER BY CustomerName"
rst.Open qry, cnn, adOpenStatic, adLockReadOnly
MsgBox rst.RecordCount & " records found", vbInformation
sh.Cells.Clear
For i = 0 To rst.Fields.Count - 1 'headers
sh.Cells(1, i + 1).Value = rst.Fields(i).Name
Next i
sh.Range("A2").CopyFromRecordset rst 'all rows in one call
sh.Columns.AutoFit
CleanUp:
On Error Resume Next
If rst.State = adStateOpen Then rst.Close
If cnn.State = adStateOpen Then cnn.Close
Set rst = Nothing: Set cnn = Nothing
Exit Sub
ErrHandler:
MsgBox "Database error " & Err.Number & ": " & Err.Description, vbCritical
Resume CleanUp
End SubThis runnable macro opens the Access file that sits beside the workbook, runs a filtered and sorted query, reports the count, writes the field names as headers, dumps every row with CopyFromRecordset and always closes both objects, even after an error.
Insert, update and delete
Action queries do not return rows, so use the connection’s Execute method:
cnn.Execute "INSERT INTO Customers (CustomerName, City) VALUES ('ABC Traders', 'Mumbai')"
cnn.Execute "UPDATE Customers SET City = 'Pune' WHERE CustomerID = 105"
cnn.Execute "DELETE FROM Customers WHERE CustomerID = 105"Execute sends the statement straight to the database; wrap user input in a parameterised ADODB.Command rather than concatenating it into the SQL to avoid injection and quoting problems.
Tips and common mistakes
- “Provider cannot be found”: the Access Database Engine is missing or its bitness (32/64-bit) does not match Office. Install the matching engine.
- RecordCount is -1 with a forward-only cursor; use adOpenStatic or adOpenKeyset when you need the count.
- Always close the Recordset and Connection; leaked connections lock Access files and exhaust SQL Server pools.
- Dates in SQL need # delimiters for Access (
#2026-09-04#) and quotes for SQL Server. - Keep the database path relative with ThisWorkbook.Path so the tool works when the folder is moved.
Practice and real-world use
Create a small Access database with a Customers table, then write one macro that lists all customers on a sheet and another that adds a new customer from three input cells. This is the exact architecture of the multi-user data-entry systems, CRM tools and inventory trackers sold on this site.
Related lessons
Frequently asked questions
Do I need Microsoft Access installed to use an .accdb database from Excel?
No. You need the Access Database Engine (ACE provider), which ships with Office and is also available as a free redistributable. Its bitness must match your Office installation.
How do I connect Excel VBA to SQL Server?
Use a connection string such as Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=SSPI, or MSOLEDBSQL for the newer driver, then open Recordsets and run Execute exactly as with Access.
Why is RecordCount -1?
The Recordset uses a forward-only cursor, which cannot count rows in advance. Open it with adOpenStatic or adOpenKeyset, or loop with MoveNext and count as you go.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.