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

Posted on 2014-09-06
Last Modified: 2014-09-07
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
    UPDATE SET Gas = source.Gas, 
               Oil = source.Oil, 
               Water = source.water
    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

Question by:Dale Fye (Access MVP)
  • 4
  • 4

Expert Comment

by:Jack Leach
ID: 40308148

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). (MERGE) (OUTPUT)

LVL 65

Accepted Solution

Jim Horn earned 300 total points
ID: 40308188
I pulled this off in a previous project.   Not sure about qdf.RecordsAffected property, but you could return the two @variables below as the SP's return set, then do what you want..

Declare @inserted int, @updated int
DECLARE @merge TABLE (action varchar(10))

-- Your above MERGE statement goes here, but remove the semi-colon and add the below line
OUTPUT $action into @merge;

-- Now you can dump the row counts into variables, and do what you want with them...
    @inserted = SUM(CASE WHEN action = 'INSERT' THEN 1 ELSE 0 END),
    @updated = SUM(CASE WHEN action = 'UPDATE' THEN 1 ELSE 0 END)

-- such as return them in the SP
SELECT @inserted as inserted_row_count, @updated as updated_row_count
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40308468

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:

Open in new window

the response = FALSE

any ideas?
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Expert Comment

by:Jack Leach
ID: 40308471
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)

Open in new window


Expert Comment

by:Jack Leach
ID: 40308473
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).
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40308490

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:
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!
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40308493

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.

Assisted Solution

by:Jack Leach
Jack Leach earned 200 total points
ID: 40308496
>> I wish they would expose the pass-thru querydef properties in a properties window!  <<

They do... open the PT in the query designer and press Alt+Enter for the properties window, and it ought to be the 3rd or 4th one down.

2010/2013 anyway, I never did much with 2007 and don't recall, but I'm pretty sure it's standard throughout versions.
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 40308568
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.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question