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)
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

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?
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!


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 48

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 48

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 48

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 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