Replace pass-through DAO.QueryDef with DAO execution of Stored Procedure Q's

[mdlueck]

I would like to test converting a pass-through query that uses DAO.QueryDef objects with code to execute a stored procedure which will perform the same SELECT statement. Reason being, I am pushing the upper limit of Access VBA string concatenation building the SQL string.

I came across this article talking about executing SP's with DAO objects:
http://support.microsoft.com/kb/184749

I am wondering how to simply replace sending pass-through SQL in a DAO object and execute the SP instead? That DAO object needs to be able to be referred to by the wrapping DAO.QueryDef object to be able to "SELECT *" from that object targeting a FE temp table as the final destination to place the selected records into.

I have coded up the SP already, now working on building the VBA code to execute the SP. I am using the following shared code to send SELECT SQL to the BE DB and download records into FE temp tables:

'This API populates the FE temp table via a query of the BE database
Public Function dbutils_RefreshLocalTmpTbl(ByVal strQueryAPIName As String, ByVal strSQLbe As String, ByVal strSQLfe As String) As Boolean
On Error GoTo Err_dbutils_RefreshLocalTmpTbl

  Dim daoDB As DAO.Database
  Dim daoQDFbe As DAO.QueryDef
  Dim daoQDFfe As DAO.QueryDef
  Dim strQryNameBE As String
  Dim strQryNameFE As String

  'Define the name for the BE query
  strQryNameBE = "vbaqry" & strQueryAPIName & "_PT"

  'Define the name for the FE query
  strQryNameFE = "vbaqry" & strQueryAPIName

  'Make sure they do not exist, delete the queries we need to build
  Call dbutils_DeleteQueryDef(strQryNameBE)
  Call dbutils_DeleteQueryDef(strQryNameFE)

  'Attach to the FE DB
  Set daoDB = CurrentDb()

[COLOR=Blue]  'Build the BE PT Query
  Set daoQDFbe = daoDB.CreateQueryDef(strQryNameBE)
  With daoQDFbe
    .Connect = ObjAppSettings.ODBCConnectString()
    .SQL = strSQLbe
    .Close
  End With[/COLOR]

  'Append the name of the BE query onto the FE SQL to complete the FROM clause
  strSQLfe = strSQLfe & " " & strQryNameBE & " AS t;"

  'Build the FE Query
  Set daoQDFfe = daoDB.CreateQueryDef(strQryNameFE)
  With daoQDFfe
    .SQL = strSQLfe
    .Execute
    .Close
  End With

  'Good return code
  dbutils_RefreshLocalTmpTbl = True

Exit_dbutils_RefreshLocalTmpTbl:
  'Delete the queries we just built
  Call dbutils_DeleteQueryDef(strQryNameBE)
  Call dbutils_DeleteQueryDef(strQryNameFE)

  'Clean up the connection to the database
  Set daoDB = Nothing
  Set daoQDFbe = Nothing
  Set daoQDFfe = Nothing

  Exit Function

Err_dbutils_RefreshLocalTmpTbl:
  'OK to expect 3325 type errors since an IF might prevent the SELECT from executing
  If Err.Number = 3325 Then
    dbutils_RefreshLocalTmpTbl = True
  Else
    'further error handling here for other error types
    Call errorhandler_MsgBox("Module: modshared_dbutils, Function: dbutils_RefreshLocalTmpTbl()")
    dbutils_RefreshLocalTmpTbl = False
  End If
  Resume Exit_dbutils_RefreshLocalTmpTbl

End Function

I would think I need to adjust the blue code to execute the SP instead, however the article I found about having DAO execute SP's is rather vague about the exact type of DAO object the example code was creating to execute the SP. Was that example even using DAO.QueryDef objects?

TIA!

 

[mdlueck]

Looking at that KB article further, it would seem that I am to end up with a DAO.Recordset object instead of a DAO.Querydef object.

So, how would I download records from the execution of the SP via the DAO.Recordsset object and store them in a FE temp table?

I was thinking DAO at first since DAO.QueryDef and Access tables work very well together. I am seeing references that to bridge from a DAO.Recordset to a FE temp table I must cursor through each of the fields for each of the result set records in VBA code... Yuck! I would prefer some solution that does not involve coded loops.

 

[mdlueck]

All right, starting to have a breakthrough.

I have the following code executing the Stored Procedure from within the SQL Server Management Studio:

DECLARE @prodid int;
SET @prodid = 1;
EXEC dbo.clsObjPartsTbl_RefreshLocalTmpTbl_All @prodid;

However, pasting that SQL into the BE DB QueryDef object and running only that QueryDef (so not being called from the wrapper QueryDef which is to INSERT the results into a FE Temp Table) results in the following error from Access:

Pass-through query with ReturnsRecords property set to True did not return any records.

Executing that SQL from the Management Studio returns records. hhhmmm....

 

[mdlueck]

Solved! Found the solution here:

Dynamic SQL not returning results in Access pass-through query
http://stackoverflow.com/questions/...eturning-results-in-access-pass-through-query

Specifically adding

[COLOR=Blue][B]SET NoCount ON;[/B][/COLOR]
DECLARE @prodid int;
SET @prodid = 1;
EXEC dbo.clsObjPartsTbl_RefreshLocalTmpTbl_All @prodid;

resolves the error. The same nested DAO.QueryDef objects are able to download selected records and place them into a FE DB temp table.

Note:

This SET NOCOUNT ON; solution may be placed in the Stored Procedure code itself. I found that such is part of the management studio's default stored procedure shell code. The SP I copied from to jump-start this new SP I had that LOC commented out as that LOC is NOT compatible with SQL UPDATE commands where I need to receive the number of records the UPDATE statement acted upon.