how to retrieve data back from pass-through query

[uoghk]

I use the Docmd.OpenQuery command for a Pass-Through query to execute a stored procedure in SQL server.


If the stored procedure return a status or a record set,
How can I retrieve these data inside VBA?

[Gicu]

Set the ReturnsRecords property to true and set a recordset based on it:
https://support.microsoft.com/en-us/...2-6dac62532a42

[uoghk]

Can u show me some lines of code?
Thanks

[Minty]

I regularly reuse a query to get results sets for forms from stored procedures or filtered views.

To that end I created this

Public Sub sSendToPT_Generic(strQuery As String, bRetRecs As Boolean)
    
    '   Description: A generic route to reuse a passthrough query for multiple purposes.
    '   It requires qPT_Generic as a passthrough query to be set up.
    '   It also relies on glStrUserTable being set to determine a backend table name used to generate the connection string
    '   You can replace that with a name of any SQL backend table.
    
    Dim db As DAO.Database
    Dim qDef As QueryDef
    
    Set db = CurrentDb()
    
    Set qDef = db.QueryDefs("qPT_Generic")
    qDef.Connect = db.TableDefs(glStrUserTable).Connect
    qDef.SQL = strQuery
    qDef.ReturnsRecords = bRetRecs
    
    If Not bRetRecs Then
        db.Execute "qPT_Generic", dbSeeChanges
    Else
        qDef.Close
    End If
    
    Set qDef = Nothing
    Set db = Nothing


End Sub

Then to use it simply either use qpt_Generic as the recordsource or set a recordset to it;

  ' Get the data into a recordset
    strSql = "EXEC [dbo].[sp_REPMonthly_Management] @dStart = " & ServerDate(dStart) & " , @dEnd = " & ServerDate(dEnd)
    
    sSendToPT_Generic strSql, True
   Set rs = CurrentDb.OpenRecordset("select * from qpt_Generic", dbOpenSnapshot)

[Gicu]

I use very similar code to Minty's, here is an example that does not return records, I use it to alter the SQL back-end tables to ensure all Bit fields are properly setup:

Public Function vcCheckBoxesSetDefaultZero()


Dim tdf As DAO.TableDef, db As DAO.Database, fld As Field
Dim sTable As String, sField As String


Set db = CurrentDb


For Each tdf In db.TableDefs


    If Left(tdf.Name, 4) <> "msys" Then
    
        'only do SQL Server tables
        If InStr(tdf.Connect, "SQL Server") > 0 Then
            For Each fld In tdf.Fields
                If fld.Type = dbBoolean Then
                    sTable = tdf.Name
                    sField = fld.Name
                    'call update to replace nulls with zeroes
                    Call vcAlterTable(tdf.Name, "UPDATE [" & sTable & "] SET [" & sField & "] = " & 0 & " WHERE [" & sField & "] IS NULL;", "tblAgents")
                    'now lets alter the table
                    Call vcAlterTable(tdf.Name, "ALTER TABLE [" & sTable & "] ALTER COLUMN [" & sField & "] BIT NOT NULL", "tblAgents")
                    Call vcAlterTable(tdf.Name, "ALTER TABLE [" & sTable & "] ADD CONSTRAINT DF_" & sTable & "_" & sField & " DEFAULT 0 FOR " & sField & ";", "tblAgents")
                End If
            Next fld
        End If
    End If
Next tdf


MsgBox "DONE"




End Function
Public Function vcAlterTable(strSourceTable As String, sAlterSQL As String, sUseConnectionStringFrom As String)
Dim db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String


On Error Resume Next


Set db = CurrentDb
If ObjectExists("QUERY", "sqlALTER_TABLE") Then DoCmd.DeleteObject acQuery, "sqlALTER_TABLE"
strSQL = sAlterSQL
Set qdExtData = db.CreateQueryDef("sqlALTER_TABLE")


qdExtData.Connect = CurrentDb.TableDefs(sUseConnectionStringFrom).Connect
qdExtData.ReturnsRecords = False
qdExtData.SQL = strSQL


CurrentDb.QueryDefs("sqlALTER_TABLE").Execute


'lets refresh the link
CurrentDb.TableDefs(strSourceTable).RefreshLink


qdExtData.Close
db.Close
Set db = Nothing
'MsgBox "ALTER TABLE STATEMENT COMPLETED - PLEASE CHECK THE TABLE IN DESIGN VIEW"
End Function

Cheers,

[uoghk]

Thanks Minty & Gicu.