David McAfee
davidmcafee at gmail.com
Thu Oct 11 14:45:53 CDT 2012
So this has come back to haunt me.
I've created a button to export the stored procedures results to an Excel
file, but the
exported records are inconsistent.
I guess when using the following line, the ADP uses an RPC as it does when
double clicking on
a sproc in the database window.
DoCmd.OutputTo acOutputStoredProcedure, strMySql, acFormatXLS,
strOutPutFile, False
Has anyone else ran into this?
The only thing I can say bout the sproc is that it is using using some #
temp tables, so maybe Access
is having a hard time with those. It shouldn't matter.
I've tried exporting the form, but since I am doing it from the header, I
don't get the detail rows.
If there was a way of getting the subform's record set and exporting it,
then I would be good.
I think I'm going to dump the values into a table and export those.
Grrrr
David
--Original thread---
A coworker wrote a new stored procedure that works just fine when ran from
SSMS.
If either of these are ran from SSMS:
EXEC stpR6Payouts '1/1/2011','11/30/2011',''
EXEC stpR6Payouts '1/1/2011','11/30/2011',NULL
The data is returned as expected.
If, the sproc is ran from VBA, or directly through the immediate
window, we get different results.
The sproc run from Access will return a few rows short, and data is
being calculated incorrectly on the rows that are returned.
Hit F5 in Access, and a different row count (and data on the returned
rows) will be different.
I've always assumed SQL was doing all the work and returning a
resultset to Access, but it doesn't appear this is actually what happens.
Does anyone have any ideas?
-------------
How does it look if you try this in a Pass-Through query?
Duane Hookom
------------------------
I created a new mdb and it returns correctly, as expected via a pass
through query.
I'm going to try a box with an Access version <2007 to test the ADP.
-----------------------
OK, I tested the ADP on a box with Access 2002.
It returned the same, incorrect row count and values.
I tried running the stored procedure from a different ADP and it also
returns incorrect records.
So far the only way to get the correct results besides running it directly
in SSMS is to run it from an mdb using a pass through query.
What occurs differently between running a pass through vs running the sproc
directly from the Access database window?
David
----------------------
OK, we ran a Trace on the different ways we are running the sproc.
When it is called from the ADP, the sproc is called via an RPC, not
directly as a passthrough query (as I've assumed it was called).
>From the ADP, if I run this:
Private Sub Command8_Click()
Dim rs As Recordset
Set rs = CurrentProject.Connection.
Execute("EXEC RRMS.dbo.stpR6Payouts '1/1/2011','11/30/2011',null")
Debug.Print rs.RecordCount
I get the correct count!
If I put an break point on the last line above and run this from the
immediate window:
rs.MoveFirst
? rs![CustName]
STAR FORD
? rs![IndividualPayCalc]
5368
I get the correct amount (that 5368 is never correct when running the sproc
from the immediate window in the ADP).
So this tells me the rendering in the ADP is having issues, correct?
This is scary. How many other things have I trusted to be correct and
weren't?
David
--------------------------
David,
Does your stored procedure specify an ORDER BY or are you leaving this to
chance?
Duane Hookom
-----------------------------------------------------------------------
Yes:
SET NOCOUNT ON
--Do a bunch of crap here
SET NOCOUNT OFF
select A.CustNo,B.cust_name AS CustName,C.State,InvTotal,TotalPayCalc,
AccuralPayCalc,DealerPayCalc,IndividualPayCalc,ARPaymentAmt
AS [PaidTo-Dealer],APAmt AS [PaidTo-Individual]
from @tblR6SumTemp A
INNER JOIN salesdb..m_customer B(NOLOCK) ON A.CustNo = B.cust_no
LEFT JOIN salesdb..m_cust_address C(NOLOCK)
on B.cust_no = C.cust_no and B.bill_address_id = C.address_id
ORDER BY CustNo
---------------------------------
David,
Are there every two records with the same CustNo in the results? If so,
would the TotalPayCalc vary?
Duane Hookom
MS Access MVP
----------------------------------------
No. One record per customer.
This is what I had to do to get it all working:
I created a datasheet subform.
In that subform, I placed a public sub:
Public Sub Type6PayoutPopulate(ByVal StartDate As String, ByVal EndDate As
String, Optional Customer As String)
Dim rs As Recordset
Set rs = CurrentProject.Connection.Execute("EXEC RRMS.dbo.stpR6Payouts '" &
Trim(StartDate) & "','" & Trim(EndDate) & "','" & Nz(Customer, "") & "'")
DoCmd.Hourglass False
If rs.RecordCount > 0 Then
Set Me.Form.Recordset = rs
Me.Visible = True
Else
Me.Visible = False
MsgBox "No records were returned"
End If
End Sub
In the Parent Form's Header, I created 3 text boxes and a command button.
I placed the following code in the button's click event:
Private Sub cmdRun_Click()
If Nz(Me.txtStart, "") = "" Or Nz(Me.txtEnd, "") = "" Then
MsgBox "Please enter a Start and End date"
Exit Sub
Else
DoCmd.Hourglass True
Call Form_frmType6PayOutDet.Type6PayoutPopulate(Me.txtStart, Me.txtEnd,
Nz(Me.txtComp, ""))
End If
End Sub
It all seems to work good now.
I still don't know why the stored procedure is being called via an RPC when
opened directly from the database window, which is causing it to render
differently.
Using CurrentProject.Connection.Execute() seems to call it correctly.
Weird.
David