VBA Recordset.MoveNext Returning E_FAIL on NULL

Question

Good day,

I have been having a problem with a bit of simple VBA. I wrote a script to take a text query (with no input parameters so no real user interaction, SQL Injection, etc.), run it against a database, and dump it to a new worksheet. This is a simple one-off for some developer analysis so the features are extremely simple.

If the query returns values for every column, there is no problem. However, if the query has any null values in it (as the result of a ROLLUP(), in my case), the entire subroutine will fail at MoveNext (NOTE: not the assignment of the null value to a cell). Originally, the script was failing before the null row was ever even accessed at

Range(Cells(2, 1), Cells(rsData.RecordCount + 1, rsData.Fields.Count)).NumberFormat = "@"

This was commented and moved to be cell-by-cell with the intent of adding a check if the current cell is null (by far the most common suggestion on the internet).

The queries have been verified using a back-end SQL editor and are correct. Every other article that I have read has either been specific to a product or not applicable. The question, then, is simply: how is one supposed to handle null values in a Recordset? I would like to avoid removing the nulls on the database side, as this sub is used for many different queries and the thought of having to pepper my queries with a bunch of NVL() statements is quite displeasing.

Thank you in advance for any assistance. Full code is as follows:

Sub runReport(query As String, sheetName As String)

    Dim cnDatabase As ADODB.Connection
    Dim rsData As ADODB.Recordset
    Dim row As Integer
    Dim column As Integer

    'Create new worksheet
    Sheets.Add.Name = sheetName
    Excel.Application.Worksheets(sheetName).Select

    'Connect to database
    Set cnDatabase = New Connection
    cnDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=DB.EXAMPLE.COM;User ID=FOO;Password=BAR;ChunkSize=1000;FetchSize=100;"
    cnDatabase.Open

    'Retrieve dataset
    Set rsData = New Recordset
    Set rsData.ActiveConnection = cnDatabase
    rsData.Source = query
    rsData.CursorLocation = adUseClient
    rsData.CursorType = adOpenStatic
    rsData.Open

    'Output header row
    For column = 1 To rsData.Fields.Count
        Cells(1, column).Value = rsData.Fields(column - 1).Name
        Rows(1).Font.Bold = True
    Next

    'Set all fields as text
    'Range(Cells(2, 1), Cells(rsData.RecordCount + 1, rsData.Fields.Count)).NumberFormat = "@"

    'Output retrieved data from database
    row = 2
    While Not rsData.EOF
      For column = 1 To rsData.Fields.Count
         Cells(row, column).NumberFormat = "@"
         Cells(row, column).Value = rsData.Fields(column - 1).Value
      Next

      rsData.MoveNext
      row = row + 1
   Wend
   cnDatabase.Close

End Sub

Answer 1

Try altering:

rsData.CursorLocation = adUseClient

to:

rsData.CursorLocation = adUseServer

(based on this archived Microsoft support article on a different Oracle issue)