VBA RecordSet Fundamentals: Query, Loop, and Write Data Using ADO Without SQL Server Dependencies

Introduction

Picture this: you've inherited a workbook that pulls sales data from a company database every morning. The macro works great — until the company migrates servers, the connection string breaks, and suddenly nobody can run the report. Your instinct might be to reach for Power Query or a direct SQL connection, but what if the data you need is already sitting in Excel worksheets or a local Access database that lives on a shared drive? What if you need to query, filter, and reshape that data programmatically, without any dependency on SQL Server, network credentials, or external IT infrastructure?

That's exactly the problem ADO Recordsets solve. ADO stands for ActiveX Data Objects, and it's a Microsoft technology built directly into Windows that lets VBA treat structured data sources — including Excel ranges, CSV files, and Access databases — as queryable databases. Instead of looping row by row through a worksheet and checking every cell manually, you write a SQL-style query and get back a clean, iterable result set. It's faster, more readable, and more powerful than brute-force cell iteration for data-heavy tasks.

By the end of this lesson, you'll be able to open an ADO connection to an Excel workbook, execute queries against worksheet data, loop through the results with a Recordset, and write the output back to a destination sheet — all without a SQL Server, network connection, or external database license.

What you'll learn:

• What ADO and Recordsets are and how they fit into the VBA ecosystem
• How to set up the ADO reference in the VBA editor and write a proper connection string for Excel files
• How to open a Recordset against a worksheet range using SQL SELECT syntax
• How to loop through Recordset rows and read field values safely
• How to write Recordset data back to a worksheet efficiently using CopyFromRecordset

---

Prerequisites

You should be comfortable writing basic VBA macros — declaring variables, calling subroutines, and working with ranges. If you need a refresher, Introduction to VBA: Write Your First Excel Macro and Automate Repetitive Tasks covers the essentials. You should also understand VBA Variables, Data Types, and Control Structures: Building Robust Excel Automation since we'll be working with several object variables at once.

---

What ADO Actually Is (And Why You Should Care)

Before writing a single line of code, it's worth understanding the architecture. ADO is a COM-based library — think of it as a universal translator between VBA and structured data. It doesn't care whether the data lives in SQL Server, an Access file, or an Excel spreadsheet. You interact with a standard set of objects regardless of the underlying source.

The three objects you'll work with most are:

• Connection — represents the open channel to the data source. Think of it like a phone line you establish before talking to the database.
• Recordset — the result of your query. It holds rows and columns of data, and you move through it one record at a time, much like a cursor in a book.
• Command — optional, used when you want parameterized queries. We'll use this briefly but won't focus on it in this foundational lesson.

The Jet/ACE database engine — the same engine that powers Microsoft Access — is what enables you to query Excel files. This engine is installed on virtually every Windows machine that has Office installed, which is exactly why ADO against Excel has zero external dependencies. You're using components that are already there.

---

Setting Up the ADO Reference

Before ADO objects are available in your VBA code, you need to enable the library reference. This is a one-time step per workbook.

Open the Visual Basic Editor by pressing Alt + F11. Then go to Tools → References in the menu bar. Scroll through the list until you find Microsoft ActiveX Data Objects 2.8 Library (or whichever is the highest numbered version available on your machine). Check the box next to it and click OK.

Once you've done this, you'll have access to the ADODB.Connection and ADODB.Recordset objects with full IntelliSense support — VBA will autocomplete their properties and methods as you type.

---

Understanding the Connection String for Excel Files

The connection string is the configuration block that tells ADO what driver to use and where the data lives. For Excel files, it looks like this:

Dim connStr As String
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=C:\Reports\SalesData.xlsx;" & _
          "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"

Let's break down each component:

• Provider — specifies the ACE engine. Use Microsoft.ACE.OLEDB.12.0 for .xlsx files. If you're on a very old system with only Jet, use Microsoft.Jet.OLEDB.4.0 for .xls files.
• Data Source — the full file path to the Excel workbook.
• Extended Properties — extra settings passed to the engine:
  • Excel 12.0 Xml tells the engine this is a modern Excel file format
  • HDR=YES means the first row of your range contains column headers (field names), which become the SQL column names in your queries
  • IMEX=1 stands for Intermixed Data — it tells the engine to treat columns that contain mixed data types as text, which prevents frustrating data truncation bugs

---

Querying Data From an External Excel File

Let's start with the most straightforward case: you have a separate Excel file called SalesData.xlsx with a sheet named Transactions. The first row has headers: OrderID, Region, Product, Amount, OrderDate.

Your goal is to query all rows where Region = 'North' and the Amount exceeds 500.

Sub QueryExternalWorkbook()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim connStr As String
    Dim sqlQuery As String
    
    ' --- Build the connection string ---
    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=C:\Reports\SalesData.xlsx;" & _
              "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
    
    ' --- Build the SQL query ---
    ' Note: Sheet names must be followed by a dollar sign and wrapped in brackets
    sqlQuery = "SELECT OrderID, Region, Product, Amount " & _
               "FROM [Transactions$] " & _
               "WHERE Region = 'North' AND Amount > 500 " & _
               "ORDER BY Amount DESC"
    
    ' --- Open connection ---
    Set conn = New ADODB.Connection
    conn.Open connStr
    
    ' --- Open the recordset ---
    Set rs = New ADODB.Recordset
    rs.Open sqlQuery, conn, adOpenStatic, adLockReadOnly
    
    ' --- Check that we have results ---
    If rs.EOF Then
        MsgBox "No records matched the query."
        GoTo Cleanup
    End If
    
    ' --- Write results to the active sheet starting at A1 ---
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Results")
    ws.Cells.ClearContents
    
    ' Write headers
    Dim i As Integer
    For i = 0 To rs.Fields.Count - 1
        ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    
    ' Write all data rows in one operation
    ws.Cells(2, 1).CopyFromRecordset rs
    
Cleanup:
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

End Sub

This is a complete, working macro. Walk through the key decisions:

Sheet name syntax: When you reference a worksheet as a table in SQL, the ACE engine requires the sheet name followed by a dollar sign (Transactions$), wrapped in square brackets. This is not optional — the dollar sign tells the engine to interpret the entire used range of that sheet as the data table.

Cursor and lock types: adOpenStatic opens the recordset with a snapshot of the data (good for reading). adLockReadOnly tells ADO we're not modifying the source. For read-only analysis tasks, this is always the right combination — it's fast and uses minimal memory.

CopyFromRecordset: This is one of the most powerful and underused methods in Excel VBA. Instead of looping row by row and writing values cell by cell, this single method dumps the entire recordset into a range starting at the specified cell. For large datasets, it's dramatically faster than any loop you could write manually.

---

Looping Through a Recordset Manually

CopyFromRecordset is perfect when you want a raw dump of data, but sometimes you need to process each row individually — applying business logic, conditional formatting, or lookups as you go. That's where manual Recordset looping comes in.

The pattern is always the same: check rs.EOF before entering, process the current record, call rs.MoveNext, repeat.

Sub ProcessRecordsetWithLogic()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim connStr As String
    Dim sqlQuery As String
    Dim ws As Worksheet
    Dim outputRow As Long
    
    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=C:\Reports\SalesData.xlsx;" & _
              "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
    
    sqlQuery = "SELECT OrderID, Region, Product, Amount FROM [Transactions$]"
    
    Set conn = New ADODB.Connection
    conn.Open connStr
    
    Set rs = New ADODB.Recordset
    rs.Open sqlQuery, conn, adOpenStatic, adLockReadOnly
    
    Set ws = ThisWorkbook.Sheets("Results")
    ws.Cells.ClearContents
    
    ' Write headers manually
    ws.Range("A1").Value = "OrderID"
    ws.Range("B1").Value = "Region"
    ws.Range("C1").Value = "Product"
    ws.Range("D1").Value = "Amount"
    ws.Range("E1").Value = "Category"  ' Derived column we calculate in VBA
    
    outputRow = 2
    
    ' Loop through every record
    Do While Not rs.EOF
    
        Dim orderAmount As Double
        orderAmount = rs.Fields("Amount").Value
        
        ' Write field values by name — more readable than by index
        ws.Cells(outputRow, 1).Value = rs.Fields("OrderID").Value
        ws.Cells(outputRow, 2).Value = rs.Fields("Region").Value
        ws.Cells(outputRow, 3).Value = rs.Fields("Product").Value
        ws.Cells(outputRow, 4).Value = orderAmount
        
        ' Apply business logic to create a derived column
        If orderAmount >= 5000 Then
            ws.Cells(outputRow, 5).Value = "Enterprise"
        ElseIf orderAmount >= 1000 Then
            ws.Cells(outputRow, 5).Value = "Mid-Market"
        Else
            ws.Cells(outputRow, 5).Value = "SMB"
        End If
        
        outputRow = outputRow + 1
        rs.MoveNext
        
    Loop
    
    MsgBox "Processed " & (outputRow - 2) & " records."
    
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

End Sub

Notice how we reference fields by name (rs.Fields("Amount").Value) rather than by index number (rs.Fields(3).Value). Using names makes the code self-documenting and immune to breaking if columns are reordered in the source data. This is the same reason experienced VBA developers prefer working with named ranges over hardcoded cell addresses.

---

Querying the Same Workbook That's Running the Macro

This is a common need and a source of confusion. You want to query data on one sheet and write results to another sheet, all within the same open workbook. ADO can do this, but you need to use the workbook's full file path — and the file must have been saved at least once (it can't be a brand-new unsaved workbook).

Sub QuerySameWorkbook()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim connStr As String
    
    ' Use ThisWorkbook.FullName to get the current file's path dynamically
    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & ThisWorkbook.FullName & ";" & _
              "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
    
    Dim sqlQuery As String
    sqlQuery = "SELECT Product, SUM(Amount) AS TotalSales " & _
               "FROM [RawData$] " & _
               "GROUP BY Product " & _
               "ORDER BY TotalSales DESC"
    
    Set conn = New ADODB.Connection
    conn.Open connStr
    
    Set rs = New ADODB.Recordset
    rs.Open sqlQuery, conn, adOpenStatic, adLockReadOnly
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Summary")
    ws.Cells.ClearContents
    
    ws.Cells(1, 1).Value = "Product"
    ws.Cells(1, 2).Value = "Total Sales"
    
    ws.Cells(2, 1).CopyFromRecordset rs
    
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

End Sub

The GROUP BY clause here is genuinely useful — you can aggregate data with SQL inside ADO even when working with plain Excel ranges. The ACE engine supports a meaningful subset of SQL including SUM, COUNT, AVG, GROUP BY, ORDER BY, and WHERE. This lets you do things that would require complex formula arrays or PivotTable automation if you stayed in the Excel object model.

---

Writing Data Back: CopyFromRecordset vs. Manual Loops

You've now seen both approaches. Here's a clear guide on when to use each:

Scenario	Use
Dump all query results to a sheet as-is	CopyFromRecordset
Apply conditional formatting to each row	Manual loop
Add derived columns based on field values	Manual loop
Need maximum speed for 10,000+ rows	CopyFromRecordset
Need to call a function on each row's data	Manual loop

For very large datasets, combining both techniques often works best: use SQL to filter and aggregate down to a manageable number of rows (reducing 100,000 records to 500 aggregated rows), then use CopyFromRecordset to write them efficiently. SQL does the heavy lifting in the engine; VBA handles the output.

---

Hands-On Exercise

This exercise builds a self-contained tool you can run immediately.

Setup: Create a new workbook. Rename Sheet1 to Employees. Add these headers in row 1: EmployeeID, Department, Salary, YearsService. Then add at least 15 rows of realistic data — mix departments (Engineering, Sales, HR, Finance), salaries between 40,000 and 120,000, and years of service between 1 and 20. Save the workbook.

Rename Sheet2 to DeptSummary.

Your task: Write a VBA macro that:

1. Opens an ADO connection to the current workbook
2. Queries the Employees sheet to return each department's average salary and headcount using GROUP BY
3. Filters to only include departments with more than 2 employees (hint: use HAVING COUNT(*) > 2)
4. Writes the results to DeptSummary with formatted headers
5. Adds a derived column in VBA: if average salary exceeds 80,000, flag it as "Above Target"; otherwise "On Track"

Stretch goal: Add error handling so that if the sheet doesn't exist or the query fails, the macro shows a descriptive message rather than crashing. The patterns in Error Handling and Debugging VBA Code Like a Pro will help you structure this cleanly.

---

Common Mistakes & Troubleshooting

"Could not find installable ISAM" This error means the ACE or Jet provider isn't found. Check that you're using the correct provider name for your Office version. For 64-bit Office, it's Microsoft.ACE.OLEDB.12.0. For 32-bit with old .xls files, try Microsoft.Jet.OLEDB.4.0. Also confirm you don't have a typo in Extended Properties — the quotes within quotes must be single quotes inside a double-quoted string.

Data is missing or truncated after the first 8 rows This is the classic IMEX problem. The ACE engine samples the first 8 rows to guess column types. If the first 8 rows have numbers and row 9 has text, the text gets dropped. The fix is IMEX=1 in your Extended Properties — confirm it's present.

"Operation is not allowed when the object is closed" You're trying to read from rs.Fields after calling rs.Close. Always complete all your data operations before closing the Recordset. Structure your code so Cleanup happens last, ideally with a GoTo Cleanup pattern after processing.

Sheet name not recognized in SQL query Remember: sheet names must end with $ and be wrapped in square brackets. [Sheet1$] works. Sheet1 does not. If your sheet name contains spaces, this is even more important: [My Data$] is the correct syntax.

The Recordset returns zero rows for data you can see on the sheet Double-check that HDR=YES matches your actual layout. If your data has no header row, use HDR=NO and reference columns as F1, F2, F3 instead of by name. Also ensure the sheet's used range actually starts at row 1, column A — data that starts elsewhere may not be picked up correctly.

---

Summary & Next Steps

You've covered a lot of ground. You now understand what ADO is and why it works without external database dependencies, how to build connection strings for Excel files using the ACE engine, how to write SQL queries against worksheet data including filtering, ordering, and aggregation, how to loop through Recordsets manually for row-level logic, and how to efficiently write results back to worksheets using both CopyFromRecordset and manual iteration.

The pattern you've learned — connect, query, loop, write, clean up — is the foundation for a wide class of Excel automation problems. Once you're comfortable with it, the natural next step is connecting to real external databases. The techniques extend directly: the SQL stays the same, only the connection string changes. Connecting Excel to External Databases with VBA: SQL Queries, ADO, and Database Automation picks up exactly where this lesson ends.

From there, you might explore using ADO Recordsets as the data backbone for UserForm-driven data entry interfaces, where the form reads from a Recordset and writes back to the source through an updateable connection. Or combine Recordset queries with automated reporting workflows that run on a schedule and drop fresh query results into a formatted template.

The core skill is solid. Now push it further.