asked on

Return records affected by SQL Stored procedure (MERGE) to Access

I'm running a SQL Server stored procedure by running a pass-thru query from Access using syntax similar to:

Set qdf = currentdb.querydefs("qry1")
qdf.SQL = "EXEC df_MyStoredProcedure NULL, '2014-09-05', NULL"
qdf.Execute

The stored procedure on the server aggregates data from several tables into a table used for reporting, and is running properly. As part of this aggregation, it uses a MERGE to simultaneously update and append records from the aggregation to the table used for reporting.

What I need to know is how to get the total number of records updated and appended (combined) back to the qdf.RecordsAffected property. The final process in the stored procedure looks like:

MERGE tbl_sysProduction AS target
USING zz_Staging_Production AS source
ON target.Entity_ID = Source.Entity_ID
AND Target.docDate = Source.DocDate
WHEN MATCHED THEN 
    UPDATE SET Gas = source.Gas, 
               Oil = source.Oil, 
               Water = source.water
WHEN NOT MATCHED THEN
    INSERT (Entity_ID, docDate, Gas, Oil, Water, DS_Stamp)
    VALUES (source.Entity_ID, source.docDate, source.Gas, source.Oil, source.Water, source.DS_Stamp)
;

Open in new window

Jack Leach

Hi,

How about using an OUTPUT clause with the MERGE execution, then writing those outputs as a return from the SP? From there, you set the ReturnsRecords property of the Access Passthrough to True, and open it as you would a regular DAO recordset (eg, Set rs = CurrentDb.OpenRecordset(qdf.Name, dbOpenSnapshot).

http://msdn.microsoft.com/en-us/library/bb510625.aspx (MERGE)
http://msdn.microsoft.com/en-us/library/ms177564.aspx (OUTPUT)

hth

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

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

Start Free Trial

Dale Fye

ASKER

Jim/Jack,

I've made the modifications recommended by Jim to my stored procedure and when I run it from SQL Server, it gives me the record counts I'm looking for.

But for some reason, I cannot seem to get the passthrough query to accept a change in the ReturnsRecords property. I've tried:

currentdb.QueryDefs("qry_SQL_PassThru_Returns_Records").returnsrecords = True

Open in new window

and that seems to be accepted (no errors), but when I try:

?currentdb.QueryDefs("qry_SQL_PassThru_Returns_Records").returnsrecords

Open in new window

the response = FALSE

any ideas?

Jack Leach

Try setting a dedicated DAO.Database object, then pulling the querydef reference from there and changing it. I think it's similar to relinking TableDefs, where it doesn't work because CurrentDB() isn't a persistent copy of the database object.

DIm db AS DAO.Database
Dim qd AS DAO.QueryDef

Set db = CurrentDb()
Set qd = db.QueryDefs(yourDef)
etc...

Open in new window

Jack Leach

Also, FWIW, I find it easier to keep a separate saved querydef for each standard PT configuration, so one that returns records and one that doesn't (and maybe a few others, I forget offhand).

Dale Fye

ASKER

Jack,

Tried that.

Public Sub SetReturnsRecords()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qry_SQL_PassThru_Returns_Records")
    qdf.ReturnsRecords = True
    Debug.Print qdf.ReturnsRecords
    Debug.Print qdf.Connect

End Sub

Open in new window

Immediate results:
False
ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=xxx-xxx;Trusted_Connection=Yes;APP=SSMA;DATABASE=xxx_System_Tables

I wish they would expose the pass-thru querydef properties in a properties window!

Dale Fye

ASKER

Jack,

re:"Also, FWIW, I find it easier to keep a separate saved querydef for each standard PT configuration"

That is how I created this on. I copied it from: qry_SQL_PassThru

Which has ReturnsRecords = False.

SOLUTION

Jack Leach

membership

This solution is only available to members.

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

Start Free Trial

Dale Fye

ASKER

Thanks, guys.

Jim, the code for the Merge worked well.

Jack, , after all these years someone finally tells me how to do that. Very intuitive, NOT. What ever happened to right-click, Properties? Changing the ReturnsRecords in the properties window worked.