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
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 66

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
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?
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI


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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

622 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