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"
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)