asked on

Excel VBA - Error trying to count records in a RecordSet

Hi. I am trying to get the number of records in my recordset in the area marked "++Get the number of records ++"
but I am getting an error. What am I doing wrong with my Excel VBA code?

       Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String
        Dim S As String
        Dim oRecordCount As Integer

    'Open Connection
        objMyConn.ConnectionString = "Driver={SQL Server Native Client 11.0};*****=no;Connection Timeout=30;"
        objMyConn.Open

    'Set and Excecute SQL Command
    
        S = "SELECT [vPerformanceH].DescriptionID, [vPerformanceH].Description, [vPerformanceH].[SAP Number]"
        S = S & ", [vPerformanceH].[Drilled From] AS [Drilled From]"
        S = S & ", [vPerformanceH].[Drilled To] AS [Drilled To],[vPerformanceH].[Drilled Total] AS Advance"
        S = S & ", '' As BlankSpace1, [vPerformanceH].Quantity As Quantity, [Descriptions].[Rate] As Rate, [vPerformanceH].Remarks"
        'S = S & ", [vPerformanceH].[Quantity] AS Quantity,[vPerformanceH].Setup AS Setups, [vPerformanceH].[Transport Hours] as [Transport Hours],[vPerformanceH].[Site] As Site, [vPerformanceH].[Angle]"
        S = S & " FROM [vPerformanceH] Left Join [Descriptions]  On ([vPerformanceH].DescriptionID = [Descriptions].ID)"
        S = S & " Where [Date] >= '" & oFromDate & "' And [Date] <= '" & oToDate & "'"
        S = S & " And [vPerformanceH].Shaft = '" & oShaft & "' And [vPerformanceH].[Hole Number] = '" & oHole & "'"
        S = S & " And [vPerformanceH].DescriptionID IN(" & strDescIDs & ")"

        strSQL = S

    'Open Recordset
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open strSQL
        
        '+++++ Get the number of records ++++++++
        If objMyRecordset.EOF Then
            oRecordCount = 0
        Else
            objMyRecordset.MoveLast
            oRecordCount = rstRecords.RecordCount
        End If
        '+++++++++++++++++++++++++++++++++++++++++

Open in new window

Rgonzo1971

Which error on which line?

Murray Brown

ASKER

"Rowset does not supporet fetching backwards" on these lines
If objMyRecordset.EOF Then
oRecordCount = 0
Else
objMyRecordset.MoveLast
oRecordCount = rstRecords.RecordCount
End If

Rgonzo1971

then try

Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.CursorType = adOpenDynamic
objMyRecordset.LockType = adLockOptimistic
objMyRecordset.Open strSQL

Open in new window

Murray Brown

ASKER

Thanks. I'm not sure where to put that into my original code

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murray Brown

ASKER

Thanks very much

Murray Brown

ASKER

Hi. I have a further question related to this if you have the time to look
https://www.experts-exchange.com/questions/29003714/Excel-VBA-Recordset-only-pulliing-through-one-record-because-of-count.html