[AccessD] [ACCESS-L] Varying results in an A2007/A2010 ADP stored procedure

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?

:/

Any ideas?

David


On Wed, Dec 21, 2011 at 12:58 PM, David McAfee <davidmcafee at gmail.com>wrote:

> 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
>
>
> On Wed, Dec 21, 2011 at 12:37 PM, David McAfee <davidmcafee at gmail.com>wrote:
>
>> 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.
>>
>>
>>
>>
>> On Wed, Dec 21, 2011 at 11:44 AM, Duane Hookom <duanehookom at hotmail.com>wrote:
>>
>>> How does it look if you try this in a Pass-Through query?
>>>
>>> Duane Hookom
>>> MS Access MVP
>>>
>>>
>>> > From: davidmcafee at GMAIL.COM
>>> >
>>> > 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?
>>>
>>>
>>> --------------------------------------------------------------------------
>>> The ACCESS-L list is hosted on a Windows(R) 2003 Server running L-Soft
>>> international's LISTSERV(R) software.  For subscription/signoff info
>>> and archives, see http://peach.ease.lsoft.com/archives/access-l.html .
>>>                             COPYRIGHT INFO:
>>> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L
>>>
>>
>>
>