Passing stored procedure as record source to access report

[kkk1979]

I have been using access as front end and SQL as Back end. I need help in generating an access report, by using a stored procedure with input parameters
as record source.
I tried the following code, but getting an error.Can't I use recordset as record source for the report.

Dim sDate As String
Dim eDate As String
Dim rsbatf As New ADODB.Recordset
 
sDate = InputBox("Enter Beginning Period", "Beginning Period")
eDate = InputBox("Enter Ending Period", "Ending Period")
 
      Set GetProc = New ADODB.Command
        cn.CursorLocation = adUseServer
        Set GetProc.ActiveConnection = cn
        GetProc.CommandType = adCmdStoredProc
        GetProc.CommandText = "dbo.FreightCostAdjustments"
        GetProc.Parameters("@startPeriod").Value = sDate
        GetProc.Parameters("@endPeriod").Value = eDate
        Set rsbatf = GetProc.Execute
    Me.RecordSource = rsbatf

can anyone help please.

[Rabbit]

GetProc doesn't seem to be defined.

[Denburt]

I have never tried to set a recordsource based on a recordset.... It has been a long day and although I have a lot of experience in each app I don't have a lot of experience utilizing SQL server from MS Access. It would seem to me that you need a difinitive view or SQl statement from SQL server then you should be O.K.

Forms

Forms and Reports in Access derive their data from queries or SELECT statements bound to the RecordSource property. In the client/server environment, the RecordSource property is based on a server-based view or on a stored procedure that is expected to return the same data as the original Access query. When you convert the record source, the same rules apply as for queries (see "Migrating Access Queries" earlier in this chapter).

http://www.microsoft.com/technet/pro....mspx?mfr=true

[ADezii]

I have been using access as front end and SQL as Back end. I need help in generating an access report, by using a stored procedure with input parameters
as record source.
I tried the following code, but getting an error.Can't I use recordset as record source for the report.

Dim sDate As String
Dim eDate As String
Dim rsbatf As New ADODB.Recordset
 
sDate = InputBox("Enter Beginning Period", "Beginning Period")
eDate = InputBox("Enter Ending Period", "Ending Period")
 
      Set GetProc = New ADODB.Command
        cn.CursorLocation = adUseServer
        Set GetProc.ActiveConnection = cn
        GetProc.CommandType = adCmdStoredProc
        GetProc.CommandText = "dbo.FreightCostAdjustments"
        GetProc.Parameters("@startPeriod").Value = sDate
        GetProc.Parameters("@endPeriod").Value = eDate
        Set rsbatf = GetProc.Execute
    Me.RecordSource = rsbatf

can anyone help please.

There is a lot to review before you even get to the Report aspect:

__1. It is an accepted programming practice not to Declare and Instantiate an Object Variable on the same line:

Dim rsbatf As New ADODB.Recordset
                  'should be
Dim rsbatf As ADODB.Recordset
Set rsbatf As New ADODB.Recordset
 
Set GetProc = New ADODB.Command
                  'should be
Dim GetProc As ADODB.Command
Set GetProc = New ADODB.Command

__2. cn.CursorLocati on = adUseServer. cn usually points to a Connection Object
and the Method to set up a Connection for this Command would be:

Set GetProc.ActiveConnection = CurrentProject.Connection

__3. Set GetProc.ActiveC onnection = cn. cn should refer to a Connection Object - where?
__4. GetProc.CursorL ocation = adUseServer. CursorLocation is a Property of a Recordset Object and is used accordingly:

MyRS.CursorLocation = adUseServer

__5. You are passing Parameters (sDate and eDate) to a Stored Procedure which probably requires a Date Data Type, yet they are declared as Strings.

[kkk1979]

I have tried some of the changes you told. But I am still getting the same compilation error at the line "Me.RecordSourc e = rsbatf " as "Type Mismatch" by highlighting ".RecordSource" .

Can't I put a recordset as RecordSource for the report ?

There is a lot to review before you even get to the Report aspect:

__1. It is an accepted programming practice not to Declare and Instantiate an Object Variable on the same line:

Dim rsbatf As New ADODB.Recordset
                  'should be
Dim rsbatf As ADODB.Recordset
Set rsbatf As New ADODB.Recordset
 
Set GetProc = New ADODB.Command
                  'should be
Dim GetProc As ADODB.Command
Set GetProc = New ADODB.Command

__2. cn.CursorLocati on = adUseServer. cn usually points to a Connection Object
and the Method to set up a Connection for this Command would be:

Set GetProc.ActiveConnection = CurrentProject.Connection

__3. Set GetProc.ActiveC onnection = cn. cn should refer to a Connection Object - where?
__4. GetProc.CursorL ocation = adUseServer. CursorLocation is a Property of a Recordset Object and is used accordingly:

MyRS.CursorLocation = adUseServer

__5. You are passing Parameters (sDate and eDate) to a Stored Procedure which probably requires a Date Data Type, yet they are declared as Strings.

[NeoPa]

I have tried some of the changes you told. But I am still getting the same compilation error at the line "Me.RecordSourc e = rsbatf " as "Type Mismatch" by highlighting ".RecordSource" .

Can't I put a recordset as RecordSource for the report ?

No.
RecordSource is the name of a record source (A string). It cannot be set to a non-string VBA program variable. It wouldn't make sense if you think about it.

[Denburt]

Can't I put a recordset as RecordSource for the report ?

Something to look at:
http://office.microsoft.com/en-us/ac...CH100621891033

Properties blocked by sandbox mode

In addition to the functions listed in the previous section, sandbox mode also blocks a number of object properties. The following table lists the objects and the blocked property or properties for each object.

Use the links in the See Also section for more help for the most commonly used properties.
Object Blocked Property or Properties
BoundObjectFram e Object
Combobox Recordset
Control Object
CurrentProject AccessConnectio n, BaseConnectionS tring, CloseConnection , Connection, OpenConnection
CustomControl Object
Form Dynaset
Hyperlink Add to favorites
Listbox Recordset
ObjectFrame Object
Report Recordset
SmartTagAction Execute
Screen ActiveDataAcces sPage

Could be an issue...

Try reducing your Automation Security Level before setting the property.

If Application.Version >= 11# Then
    Application.AutomationSecurity = 1 ' msoAutomationSecurityLow
End If

[Hans Payini]

I tried it this way an it works ok.

Private Sub Report_Open(Cancel As Integer)
      Dim cmd As ADODB.Command
      Set cmd = New ADODB.Command
 
      cmd.ActiveConnection = Application.CurrentProject.Connection
      cmd.CommandType = adCmdStoredProc
      cmd.CommandText = "Name of the Store Procedure"
 
      'Your parameters
      cmd.Parameters("@startdate").Value = "1/1/1"
      cmd.Parameters("@finaldate").Value = "7/7/7"
 
      Set Me.Recordset = cmd.Execute
End Sub

I hope it helps...

[ADezii]

I tried it this way an it works ok.

Private Sub Report_Open(Cancel As Integer)
      Dim cmd As ADODB.Command
      Set cmd = New ADODB.Command
 
      cmd.ActiveConnection = Application.CurrentProject.Connection
      cmd.CommandType = adCmdStoredProc
      cmd.CommandText = "Name of the Store Procedure"
 
      'Your parameters
      cmd.Parameters("@startdate").Value = "1/1/1"
      cmd.Parameters("@finaldate").Value = "7/7/7"
 
      Set Me.Recordset = cmd.Execute
End Sub

I hope it helps...

In your workable code, the ActiveConnectio n Property of the Command Object is clearly defined as in:

cmd.ActiveConnection = Application.CurrentProject.Connection

whereas in your non-workable version:

Set GetProc.ActiveConnection = cn    'what does cn point to

try

Set GetProc.ActiveConnection = CurrentProject.Connection

[kkk1979]

Hi,
When I tried the statement
Me.Recordset = cmd.Execute
getting an error "THis feature is not available in an mdb".
But I can now able to get result into a record set(with your code). Thanks a lot for that. I am able to send the values into temporary variables but not into report controls.How do I get the values into report controls. Can you help me pls.

Thanks in advance

In your workable code, the ActiveConnectio n Property of the Command Object is clearly defined as in:

cmd.ActiveConnection = Application.CurrentProject.Connection

whereas in your non-workable version:

Set GetProc.ActiveConnection = cn    'what does cn point to

try

Set GetProc.ActiveConnection = CurrentProject.Connection