[AccessD] How Access actually accesses SQL Server data

I had a weird issue last month with an ADP.

A coworker wrote a new stored procedure that worked just fine when ran from
SSMS.

If either of these were run from SSMS:

EXEC stpR6Payouts '1/1/2011','11/30/2011',''

EXEC stpR6Payouts '1/1/2011','11/30/2011',NULL

The data was returned as expected.


If the sproc was run from VBA, or directly through the immediate window, we
experienced different results.
If the sproc was run from Access, it would return a few rows short, and
data was being calculated incorrectly on the rows that are returned.
If F5  was pressed in Access, a different row count (and data on the
returned rows) would appear.

I tested the ADP in different versions of Access to see if it was a problem
in A2007/2010, but it behaved the same in Access 2003.
I tried running the stored procedure from a different ADP and it behaved
the same.

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.
This told us that the rendering in the ADP was the issue.
I had to determine what occurs differently between running a pass through
vs running the sproc directly from the Access database window.
So we ran a trace on the different ways we are calling 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).

The SPROC was basically this:

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


This ended up being my work around:

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

I still don't know why the stored procedure is being called via an RCP when
opened directly from the database window, which is causing it to render
differently.

Using CurrentProject.Connection.Execute() seems to call it correctly.


Weird.




On Thu, Jan 19, 2012 at 7:39 AM, Mark Simms <marksimms at verizon.net> wrote:

> My benchmarks using AC2003 a few years ago:
> Query Select speed: 2 to 3x faster with SQL Server
> Insert/Update speed: 5-10x slower with SQL Server.
>
> Always use SPROCS for inserts/updates....and probably deletes.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>