Stored Proc running incorrectly from Access adp

J

JR

I have an Access adp that I am using to create reports from an SQL database.
All was fine until we "upgraded" to Access 03.

My stored procedure takes 3 parameters and creates a simple table showing
data by month for all months between the start and end dates.

In Access I am running my stored procedure and passing parameters as follows:

DoCmd.RunSQL "sp_MonthlySubStatusReportSummary '" & stGroupPublicID & "', '"
& dtStartDate & "', '" & dtEndDate & "'"

I also run this stored procedure in Query Analyzer as follows:

sp_MonthlySubStatusReportSummary '2050', '1/1/2005', '6/30/2005'

When I run the SP from Query Analyzer it works fine. I get six rows in my
table and all the data is correct.

When I run the SP from Access I get one row of data (January) and all other
fields are null. The parameters are being passed correctly.

I don't get an error message and the SP runs part way, enough to clear out
the table and start to repopulate it.

The SP is fairly straight forward (IF statements, WHILE loops, etc) and
everything that is in there is in another SP that is working fine.

I've been working on this for a long time and I'm stumped. I've chagned the
WHILE loop to a cursor and my #temp tables to real tables but the result is
the same.

If anyone has any ideas I'd be grateful for any help.

Thanks
Jeff
 
O

Ofer

If you want to try a different approach, create a pass through query called
"Run_Action_PassThrough" , link it to your DB, and build this function,

Function Run_Pass_Through(SqlStr As String) As Integer
On Error GoTo Run_Pass_Through_Err

Dim Quy As QueryDef
Dim db As Database

Set db = CodeDb()
Set Quy = db.QueryDefs("Run_Action_PassThrough")
Quy.sql = SqlStr
DoCmd.OpenQuery "Run_Action_PassThrough"

Run_Pass_Through = True

Run_Pass_Through_Ext:
Exit Function

Run_Pass_Through_Err:
MsgBox Error
Run_Pass_Through = False
Resume Run_Pass_Through_Ext

End Function
===============================
Now call the Function

Run_Pass_Through "sp_MonthlySubStatusReportSummary '" & stGroupPublicID &
"', '" & dtStartDate & "', '" & dtEndDate & "'"

==================================
 
Top