ADODB recordset recordcount always returns -1

Question

I am trying to retrieve data to excel form a database in MS access. However the recordcount property for recordset always return -1 though for other purposes the code is working fine.

The code I am using is as follows :

Sub datarecordset()

    Dim cn As ADODB.Connection
    Dim oRs As ADODB.Recordset
    Set cn = CreateObject("ADODB.Connection")
    DBPath = "C:\[databse path]" & "\[database name].accdb"
    dbWs = "[excel sheet name]"
    scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath
    dsh = "[" & "[excel sheet name]" & "$]"
    cn.Open scn
    Dim sSQL As String
    
    Dim F As Integer
    
    sSQL = "Select 'W',a.[Subledger],NULL,sum(a.[Amount]) from GL_Table a where a.[Opex_Group] = 10003 and year(a.[G/L Date]) = " & Year(Sheets("Repairs").Cells(1, 4)) & " and month(a.[G/L Date]) = " & Month(Sheets("Repairs").Cells(1, 4))
    sSQL = sSQL & " group by " & "a.[Subledger],(year(a.[G/L Date])),(month(a.[G/L Date]))"
    Set oRs = cn.Execute(sSQL)
    Debug.Print oRs.RecordCount
    oRs.Close
    ....... further code to print to excel here
    
    cn.Close
End Sub

The code will fetch data in recordset and write in excel. But since the recordset property is not returning the recordcount so can't print values of various fields in recordset to different cells of excel worksheet.

I searched on google and understood that I need to declare the recordset type and for that I have to use connection.open in place of connection.execute. But I am trying to change the code then it gives the error object variable or With variable not defined.

Answer 1

The link by @BitAccesser provides a valid solution. Quick how-to-implement in your situation: Instead of Set oRs = cn.Execute(sSQL)

Set oRS = CreateObject("ADODB.Recordset")
oRS.CursorLocation = adUseClient
oRS.Open sSQL, cn

ADO's recordcount property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. That last one is true for this case.

To avoid this, there are several solutions. The most simple one is to use a client-side cursor, which I just demonstrated, but more alternative solutions are provided in the links by @BitAccesser