VBA ADO Recordset Loop Through Values


New Member
Nov 6, 2019
Hi everyone - I'm using vba to create an ADO recordset based on a named range in the current workbook, and I'm using this overall procedure to create an MS word form with content controls. I am able to create the connection and recordset, when I do a recordcount I get 30 records, which is what I expect, but for some reason, the assignment of the value from the record isn't working - I've tried rs(0), rs("Service_Detail_2"), rs.Fields("Service_Detail_2").Value, and rsProcedure![Service_Detail_2] where Service_Detail_2 is the column header in the dataset, but I'm getting a null result each time, and an error message of Invalid Use of Null when I try to assign the expected result to a variable for test purposes. I can't seem to figure this out in any searching and I'm guessing it's just a syntax error or something little that is blocking this - any help is greatly appreciated! What's also interesting is if I add the line of code - On Error Resume Next - above the TestMe variable that triggers the null error, I would expect the procedure to run through each of the 30 records and end without providing the result I want, but it goes into an infinite loop. This makes me thing the problem isn't with the commands I'm trying to get the variable, but actually with something else related to the recordset or loop. Thanks!

Here's the sample code section for reference:

Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
Public myRecordCount As Long

'Create the connection...
Set con = CreateObject("ADODB.Connection")
con.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0 Macro;HDR=YES"";"

'Open the recordset...
sql = "SELECT Distinct Service_Detail_2 FROM [Procedures]" 'Procedures is the named range for the dataset in the current workbook

Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, con, adOpenStatic, adLockReadOnly

'If the recordset is empty...
If rs.EOF Then
Exit Sub
End If

'With the recordset...
With rs
myRecordCount = .RecordCount

Do Until .EOF ' Do until/loop until end of field i.e., last record
'Test the result to confirm ability to pull the value from the recordset for assignment in Word
Dim TestMe As String
TestMe = rs(0) 'rs("Service_Detail_2") 'rs.Fields("Service_Detail_2").Value 'rs![Service_Detail_2]
End with

'Clean up the recordset...
Set rs = Nothing

'Clean up the connection....
Set con = Nothing

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.


Well-known Member
May 27, 2010
like this:
VBA Code:
    RS.Open Sql, con, adOpenForwardOnly, adLockReadOnly
    If Not RS.EOF Then
        Do While Not RS.EOF
    End If

Watch MrExcel Video

Forum statistics

Latest member