Using SQL Stored Proc as RecordSource in Access FE 2

[VicM]

Hi
I recently inherited an Access split DB and migrated the BE to SQL Server. The Access FE has many queries which in turn call queries, etc., so I decided to write views and stored procedures in SQL to help with the performance hit of the complex queries.

I need to apply a date range filter on the records which is supplied by entry in a pop-up form called from the report's Open event.

I can create a view and use that as a source in a query and apply the entered date range that way, but the view has several tens of thousands of records and there is still a performance hit.

So I wrote a stored procedure using two parameters for the date range.

What I'm not sure about is how to call the SQL stored proc as the RecordSource for the report.

I'm using MSAccess2003 and SQL Server 2005

Any help would be appreciated.

Thanks,
Vic

 

[PWise]

is the FE a mdb or a adp?

 

[VicM]

The FE is an mdb.

 

[PWise]

What you can do is create a pass thru query

SpNaem ,pram1 ,pram2

and use this code to change the query

currentdb.QueryDefs("queryname").SQL = "Spname 'Startdate' , 'enddate'

 

[Hap007]

Hi,

You could try something like this:
On Error GoTo CS_ERR
Dim db As Database
Dim ErrCount As Integer, GoodCount As Integer
Dim QD As QueryDef, ConnectString As String, RecCount As Long, STDCount As Long

Set db = CodeDb

ConnectString = SQLConnectString
Set QD = db.CreateQueryDef("")
QD.Connect = ConnectString
QD.ReturnsRecords = False

TheID = rs![CustomerID]
TheString = "sp_AR_CustStats " & YTDsales & ", " & TheID
QD.SQL = TheString
QD.Execute


or ------------------------------------------------------

You could try:

Dim QD As QueryDef, ConnectString As String
Dim rs As Recordset

'Need to condition the user name to send to the stored procedure
Dim UserStr As String
UserStr = "'" & SQLFixup(CurrentUser()) & "'"

ConnectString = SQLConnectString()
Set DB = CodeDb
Set QD = DB.CreateQueryDef("")
QD.Connect = ConnectString
QD.SQL = "sp_GetNextBatch " & UserStr
QD.ReturnsRecords = True
Set rs = QD.OpenRecordset()
GetNextBatch = rs![NewBatch]
NewBatch = rs![NewBatch]
rs.Close

Note: These are just samples, you will need to properly define data, recordsets and the 'Connection String'

Hope This Helps,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[VicM]

pwise,

I can create the pass-thru query accordingly:

usp_BillList '7/9/08', '7/9/08'

it returns the correct data.

But I'm still confused on how to insert the dates that are selected from the pop-up form that is opened from the report's Open event.

If I try:

usp_BillList [Forms]![frmBillList].[StartDate], [Forms]![frmBillList].[EndDate]

I get an error of Incorrect syntax near !

How do I feed the dates from the pop-up form to the pass-thru query?

Thanks,
Vic

 

[PWise]

if you are useing this sp for a report i would do this diffrent

create a module called modado

Option Compare Database
Option Explicit
Public CnnAlt As New ADODB.Connection
Public Cnn As New ADODB.Connection
Dim cmd As New ADODB.Command

Function InitializeAdo()
If Cnn.State = adStateClosed Then
    Cnn.ConnectionTimeout = 0
    Cnn.Open "your Connection String"
End If
End Function
Function ExecuteAdoRS(AdoString As String, adoCommandType As Integer, ParamArray AdoPrams()) As ADODB.Recordset

'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim a As Integer
InitializeAdo
cmd.CommandText = AdoString
Set cmd.ActiveConnection = Cnn
cmd.CommandType = adoCommandType
cmd.CommandTimeout = 0
For Prams = 0 To UBound(AdoPrams)
     cmd.Parameters(Prams) = AdoPrams(Prams)
    'Debug.Print cmd.Parameters.Item(Prams).Name, Prams, cmd.Parameters.Item(Prams).Value
Next Prams
Set ExecuteAdoRS = cmd.Execute(a)
If adoCommandType = 4 Then AdoPrams(0) = cmd(0)
End Function

and in the report open

Set me.recordset =ExecuteAdoRS("usp_BillList", 4, 0,[Forms]![frmBillList].[StartDate], [Forms]![frmBillList].[EndDate])

 

[VicM]

pwise,

Thanks for you input.

I've instituted the code you suggested. It looks like it should work just fine. However, when the execution gets to the following code line:

     cmd.Parameters(Prams) = AdoPrams(Prams)

I get the following error:

Runtime error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

Suggestions?

Please note: I am not conversant in ADO. I came from the DAO school and never migrated up.

Thanks,
Vic

 

[PWise]

what is the value of pram when youget this error?


if your sp does not have a return value call the sp like this

Set me.recordset =ExecuteAdoRS("usp_BillList", 4, [Forms]![frmBillList].[StartDate], [Forms]![frmBillList].[EndDate])

 

[JoeAtWork]

PWise - My understanding is that, unlike a form, you can not dynamically set the recordset of a report. This may have changed for Access2007, but for Access2003 everything I have read, including Microsoft articles, indicated this could not be done.

Have you actually had success with setting the recordset of a report? If so, is there a certain service pack for Access2003 required?

 

[PWise]

I dont remember in what version of access this changed. Right now I am sitting @ Access 2003 SP3 and this works.

See Applies to in MShelp for Recordset Property

 

[VicM]

pwise,

The execution went further, but now if fails on the following line:

Me.Recordset = ExecuteAdoRS("usp_BillList", 4, 0, [Forms]![frmBillList].[StartDate], [Forms]![frmBillList].[EndDate])

With the following error:

This feature is not available in an MDB.

I also have MSAccess 2003 SP3. Is there an addin I need to install?

Thanks,
Vic

 

[PWise]

joe - vic

Sorry you are right this does not work in a mdb only in a adp!


let us go back to the first way with a pass true query


use this code to change you query

dim mydb as database
set mydb=currentdb
mydb.querydefs("queryname").sql = "usp_BillList '" & [Forms]![frmBillList].[StartDate] & "','" & [Forms]![frmBillList].[EndDate] & "'"

 

[VicM]

pwise, joe:

It took a little more finagling, but I finally got it working. And learned something in the process!

I appreciate your input and need to pass some stars along.

Thanks again,
Vic

 

[PWise]

Glad to be of help