CODEWITHSUNDEEP
ms-accessstored-proceduresvbams-access-2013
StealthRT
StealthRT

Reputation: 10542

Access 2013 with SQL Stored Procedure function recordset return

Hey all I am trying to make a module that has a call to my MS SQL databases' stored proceedure.

Problem is, it doesnt seem to transfer what it finds as a recordset back to the main function that called it.

Main form code:

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
Set rst = dbcon.dbConnection("StoredProcNameHere")
Debug.Print rst(0) '<-- has the number needed here
if (rst.RecordCount > 0) then...

And my module code:

Public Function dbConnection(proc As String, Optional parm As String)
   Dim con As ADODB.Connection
   Dim rst As ADODB.Recordset

   Set con = New ADODB.Connection
   Set rst = New ADODB.Recordset

   con.ConnectionString = "Provider=SQLOLEDB; Data Source=.....etc etc"
   con.Open

   If parm <> null then
      cmd.Parameters(0).Value = parm
   End If 

   Set rst = con.Execute("EXEC " & proc)
   Debug.Print rst(0) '<-- has the number needed here
   Set dbConnection = rst
End Function

When doing this, having a break point inside the dbConnection, I have a value for Debug.Print rst(0) and also still have a value when hovering over the Debug.Print rst(0) inside the Main Form code. However, when it gets to the If statement (if (rst.RecordCount > 0)) it has a value of -1???

I don't want to just return rst(0) as a String since some of the store proceedures will have multipule rows coming back instead of just one.

Upvotes: 1

Views: 480

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56725

As noted in the doc (https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/recordcount-property-ado), the RecordCount property is unreliable for this purpose:

The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount.

Instead, use the BOF and EOF properties with MoveFirst/MoveNext to accomplish this, as described here: https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/bof-eof-properties-ado

And here : https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/bof-eof-and-bookmark-properties-example-vb:

Set rs = CurrentDb.OpenRecordset(strSQL)

With rs
    If Not .BOF And Not .EOF Then

        .MoveFirst

        While (Not .EOF)
            Debug.Print rs.Fields("...")
            .MoveNext
        Wend

    End If

    .close
End With

Upvotes: 1

Related Questions