473,383 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,383 software developers and data experts.

Passing stored procedure as record source to access report

23
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.

Expand|Select|Wrap|Line Numbers
  1. Dim sDate As String
  2. Dim eDate As String
  3. Dim rsbatf As New ADODB.Recordset
  4.  
  5. sDate = InputBox("Enter Beginning Period", "Beginning Period")
  6. eDate = InputBox("Enter Ending Period", "Ending Period")
  7.  
  8.       Set GetProc = New ADODB.Command
  9.         cn.CursorLocation = adUseServer
  10.         Set GetProc.ActiveConnection = cn
  11.         GetProc.CommandType = adCmdStoredProc
  12.         GetProc.CommandText = "dbo.FreightCostAdjustments"
  13.         GetProc.Parameters("@startPeriod").Value = sDate
  14.         GetProc.Parameters("@endPeriod").Value = eDate
  15.         Set rsbatf = GetProc.Execute
  16.     Me.RecordSource = rsbatf
can anyone help please.
Apr 10 '07 #1
17 21117
Rabbit
12,516 Expert Mod 8TB
GetProc doesn't seem to be defined.
Apr 12 '07 #2
Denburt
1,356 Expert 1GB
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
Apr 12 '07 #3
ADezii
8,834 Expert 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. Dim sDate As String
  2. Dim eDate As String
  3. Dim rsbatf As New ADODB.Recordset
  4.  
  5. sDate = InputBox("Enter Beginning Period", "Beginning Period")
  6. eDate = InputBox("Enter Ending Period", "Ending Period")
  7.  
  8.       Set GetProc = New ADODB.Command
  9.         cn.CursorLocation = adUseServer
  10.         Set GetProc.ActiveConnection = cn
  11.         GetProc.CommandType = adCmdStoredProc
  12.         GetProc.CommandText = "dbo.FreightCostAdjustments"
  13.         GetProc.Parameters("@startPeriod").Value = sDate
  14.         GetProc.Parameters("@endPeriod").Value = eDate
  15.         Set rsbatf = GetProc.Execute
  16.     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:
Expand|Select|Wrap|Line Numbers
  1. Dim rsbatf As New ADODB.Recordset
  2.                   'should be
  3. Dim rsbatf As ADODB.Recordset
  4. Set rsbatf As New ADODB.Recordset
  5.  
  6. Set GetProc = New ADODB.Command
  7.                   'should be
  8. Dim GetProc As ADODB.Command
  9. Set GetProc = New ADODB.Command
__2. cn.CursorLocation = adUseServer. cn usually points to a Connection Object
and the Method to set up a Connection for this Command would be:
Expand|Select|Wrap|Line Numbers
  1. Set GetProc.ActiveConnection = CurrentProject.Connection
__3. Set GetProc.ActiveConnection = cn. cn should refer to a Connection Object - where?
__4. GetProc.CursorLocation = adUseServer. CursorLocation is a Property of a Recordset Object and is used accordingly:
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 12 '07 #4
kkk1979
23
I have tried some of the changes you told. But I am still getting the same compilation error at the line "Me.RecordSource = 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:
Expand|Select|Wrap|Line Numbers
  1. Dim rsbatf As New ADODB.Recordset
  2.                   'should be
  3. Dim rsbatf As ADODB.Recordset
  4. Set rsbatf As New ADODB.Recordset
  5.  
  6. Set GetProc = New ADODB.Command
  7.                   'should be
  8. Dim GetProc As ADODB.Command
  9. Set GetProc = New ADODB.Command
__2. cn.CursorLocation = adUseServer. cn usually points to a Connection Object
and the Method to set up a Connection for this Command would be:
Expand|Select|Wrap|Line Numbers
  1. Set GetProc.ActiveConnection = CurrentProject.Connection
__3. Set GetProc.ActiveConnection = cn. cn should refer to a Connection Object - where?
__4. GetProc.CursorLocation = adUseServer. CursorLocation is a Property of a Recordset Object and is used accordingly:
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 16 '07 #5
NeoPa
32,556 Expert Mod 16PB
I have tried some of the changes you told. But I am still getting the same compilation error at the line "Me.RecordSource = 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.
Apr 17 '07 #6
Denburt
1,356 Expert 1GB
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
BoundObjectFrame Object
Combobox Recordset
Control Object
CurrentProject AccessConnection, BaseConnectionString, CloseConnection, Connection, OpenConnection
CustomControl Object
Form Dynaset
Hyperlink Add to favorites
Listbox Recordset
ObjectFrame Object
Report Recordset
SmartTagAction Execute
Screen ActiveDataAccessPage
Could be an issue...

Try reducing your Automation Security Level before setting the property.
Expand|Select|Wrap|Line Numbers
  1. If Application.Version >= 11# Then
  2.     Application.AutomationSecurity = 1 ' msoAutomationSecurityLow
  3. End If
Apr 17 '07 #7
I tried it this way an it works ok.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.       Dim cmd As ADODB.Command
  3.       Set cmd = New ADODB.Command
  4.  
  5.       cmd.ActiveConnection = Application.CurrentProject.Connection
  6.       cmd.CommandType = adCmdStoredProc
  7.       cmd.CommandText = "Name of the Store Procedure"
  8.  
  9.       'Your parameters
  10.       cmd.Parameters("@startdate").Value = "1/1/1"
  11.       cmd.Parameters("@finaldate").Value = "7/7/7"
  12.  
  13.       Set Me.Recordset = cmd.Execute
  14. End Sub
I hope it helps...
May 8 '07 #8
ADezii
8,834 Expert 8TB
I tried it this way an it works ok.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.       Dim cmd As ADODB.Command
  3.       Set cmd = New ADODB.Command
  4.  
  5.       cmd.ActiveConnection = Application.CurrentProject.Connection
  6.       cmd.CommandType = adCmdStoredProc
  7.       cmd.CommandText = "Name of the Store Procedure"
  8.  
  9.       'Your parameters
  10.       cmd.Parameters("@startdate").Value = "1/1/1"
  11.       cmd.Parameters("@finaldate").Value = "7/7/7"
  12.  
  13.       Set Me.Recordset = cmd.Execute
  14. End Sub
I hope it helps...
In your workable code, the ActiveConnection Property of the Command Object is clearly defined as in:
Expand|Select|Wrap|Line Numbers
  1. cmd.ActiveConnection = Application.CurrentProject.Connection
whereas in your non-workable version:
Expand|Select|Wrap|Line Numbers
  1. Set GetProc.ActiveConnection = cn    'what does cn point to
try
Expand|Select|Wrap|Line Numbers
  1. Set GetProc.ActiveConnection = CurrentProject.Connection
May 8 '07 #9
kkk1979
23
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 ActiveConnection Property of the Command Object is clearly defined as in:
Expand|Select|Wrap|Line Numbers
  1. cmd.ActiveConnection = Application.CurrentProject.Connection
whereas in your non-workable version:
Expand|Select|Wrap|Line Numbers
  1. Set GetProc.ActiveConnection = cn    'what does cn point to
try
Expand|Select|Wrap|Line Numbers
  1. Set GetProc.ActiveConnection = CurrentProject.Connection
Jul 30 '07 #10
ADezii
8,834 Expert 8TB
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
I performed a major Face Lift on the code and I think this will work. Let me know how you make out (Open() Event of Report).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. Dim sDate As String, eDate As String
  3.  
  4. sDate = InputBox("Enter Beginning Period", "Beginning Period")
  5. eDate = InputBox("Enter Ending Period", "Ending Period")
  6.  
  7. 'Cannot be Empty Strings or Nulls
  8. If Len(sDate) = 0 Or Len(eDate) = 0 Then
  9.   Cancel = True
  10.     Exit Sub
  11. End If
  12.  
  13. 'Must be valid Dates
  14. If Not IsDate(sDate) Or Not IsDate(eDate) Then
  15.   Cancel = True
  16.     Exit Sub
  17. End If
  18.  
  19. Dim GetProc As ADODB.Command
  20. Set GetProc = New ADODB.Command
  21.  
  22. With GetProc
  23.   'automatically maintained for *.adps
  24.   .ActiveConnection = CurrentProject.Connection
  25.   .CommandType = adCmdStoredProc
  26.   .CommandText = "dbo.FreightCostAdjustments"
  27.     .Parameters("@startPeriod").Value = sDate
  28.     .Parameters("@endPeriod").Value = eDate
  29. End With
  30.  
  31. Set Me.Recordset = GetProc.Execute
  32. Set GetProc = Nothing
  33. End Sub
Jul 31 '07 #11
kkk1979
23
I am very thank ful to you.
I think the error might be the access problem, It likes the whole code except
Set Me.Recordset = GetProc.Execute .
Getting the same error This feature is not available in an MDB, Run-time error '2593'. Can you figure out that.....please

Thanks again for spending your valuable time on this problem.

I performed a major Face Lift on the code and I think this will work. Let me know how you make out (Open() Event of Report).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. Dim sDate As String, eDate As String
  3.  
  4. sDate = InputBox("Enter Beginning Period", "Beginning Period")
  5. eDate = InputBox("Enter Ending Period", "Ending Period")
  6.  
  7. 'Cannot be Empty Strings or Nulls
  8. If Len(sDate) = 0 Or Len(eDate) = 0 Then
  9.   Cancel = True
  10.     Exit Sub
  11. End If
  12.  
  13. 'Must be valid Dates
  14. If Not IsDate(sDate) Or Not IsDate(eDate) Then
  15.   Cancel = True
  16.     Exit Sub
  17. End If
  18.  
  19. Dim GetProc As ADODB.Command
  20. Set GetProc = New ADODB.Command
  21.  
  22. With GetProc
  23.   'automatically maintained for *.adps
  24.   .ActiveConnection = CurrentProject.Connection
  25.   .CommandType = adCmdStoredProc
  26.   .CommandText = "dbo.FreightCostAdjustments"
  27.     .Parameters("@startPeriod").Value = sDate
  28.     .Parameters("@endPeriod").Value = eDate
  29. End With
  30.  
  31. Set Me.Recordset = GetProc.Execute
  32. Set GetProc = Nothing
  33. End Sub
Jul 31 '07 #12
ADezii
8,834 Expert 8TB
I am very thank ful to you.
I think the error might be the access problem, It likes the whole code except
Set Me.Recordset = GetProc.Execute .
Getting the same error This feature is not available in an MDB, Run-time error '2593'. Can you figure out that.....please

Thanks again for spending your valuable time on this problem.
I think that I may finally have the solution, if I am correct you are unfortunately in trouble unless you upgrade.
The capability of setting a Report's Recordset Property programmatically is NEW in Access 2002.
If you are using a Version of Access prior to 2002, you are in trouble. Let me know how you make out.
Jul 31 '07 #13
kkk1979
23
I am using access 2000. But if I kept a "record set" , instead of me.recordset like the following.
set rsbatf = GetProc.Execute
Now I can access the return values into variables of report in code.
But my problem is how do I get the values of variables into report controls(text boxes or labels).Is there any way to get the report variables into report controls.

Thanks



I think that I may finally have the solution, if I am correct you are unfortunately in trouble unless you upgrade.

If you are using a Version of Access prior to 2002, you are in trouble. Let me know how you make out.
Jul 31 '07 #14
ADezii
8,834 Expert 8TB
I am using access 2000. But if I kept a "record set" , instead of me.recordset like the following.
set rsbatf = GetProc.Execute
Now I can access the return values into variables of report in code.
But my problem is how do I get the values of variables into report controls(text boxes or labels).Is there any way to get the report variables into report controls.

Thanks
Once a Recordset has been create via GetProc.Execute, you should be able to treat it like any other Recordset, namely:
Expand|Select|Wrap|Line Numbers
  1. Set rsbatf = GetProc.Execute 
  2. rsbatf.MoveLast: rsbatf.MoveFirst
  3.  
  4. Debug.Print rsbatf.RecordCount      'will give your the Number of Records
  5.  
  6. With rsbatf
  7.   Do While Not .EOF            'loop through all the Records
  8.     Debug.Print ![Field1]
  9.     Debug.Print ![Field2]
  10.     Debug.Print ![Field3]
  11.     Debug.Print ![...]
  12.       .MoveNext
  13.   Loop
  14. End With
  15.  
  16. rsbatf.Close
But my problem is how do I get the values of variables into report controls(text boxes or labels).Is there any way to get the report variables into report controls.
Is your question how do I now populate the Fields (Labels and Text Boxes) in the Detail Section with appropriate values from the Recordset? If this is correct, then the mechanism to do this is probably very complex, which is probably why this capability didn't exist until Access 2002. An off-the-wall solution would be to add all the Records from the Recordset (rsbatf) to a Table which would then be the RecordSource for the Report. I think that I would hold off on this approach for awhile and see if someone comes up with a better solution. If you do want to take this unorthodox approach, I'll need the Field Names in the rsbatf Recordset as well as their Data Types and the Name of the Table which will be the RecordSource for the Report.
Jul 31 '07 #15
kkk1979
23
Yes, I did the same. I sent all the values into temporary variables and then used those values to populate labels on the report. But in this process it is not allowing textboxes to populate.
Until I get better solution, I will keep this code.

Thanks a lot for spending your valuble time on this problem. With out your code I may not able to get the record set.



Once a Recordset has been create via GetProc.Execute, you should be able to treat it like any other Recordset, namely:
Expand|Select|Wrap|Line Numbers
  1. Set rsbatf = GetProc.Execute 
  2. rsbatf.MoveLast: rsbatf.MoveFirst
  3.  
  4. Debug.Print rsbatf.RecordCount      'will give your the Number of Records
  5.  
  6. With rsbatf
  7.   Do While Not .EOF            'loop through all the Records
  8.     Debug.Print ![Field1]
  9.     Debug.Print ![Field2]
  10.     Debug.Print ![Field3]
  11.     Debug.Print ![...]
  12.       .MoveNext
  13.   Loop
  14. End With
  15.  
  16. rsbatf.Close
Is your question how do I now populate the Fields (Labels and Text Boxes) in the Detail Section with appropriate values from the Recordset? If this is correct, then the mechanism to do this is probably very complex, which is probably why this capability didn't exist until Access 2002. An off-the-wall solution would be to add all the Records from the Recordset (rsbatf) to a Table which would then be the RecordSource for the Report. I think that I would hold off on this approach for awhile and see if someone comes up with a better solution. If you do want to take this unorthodox approach, I'll need the Field Names in the rsbatf Recordset as well as their Data Types and the Name of the Table which will be the RecordSource for the Report.
Aug 1 '07 #16
ADezii
8,834 Expert 8TB
Yes, I did the same. I sent all the values into temporary variables and then used those values to populate labels on the report. But in this process it is not allowing textboxes to populate.
Until I get better solution, I will keep this code.

Thanks a lot for spending your valuble time on this problem. With out your code I may not able to get the record set.
It's a shame that you can't upgrade to Access 2002, we wouldn't even be having this conversation.
Aug 1 '07 #17
Shals
2
Hi,
I'm trying to use the same functionality in my report.
I tried the code it works fine within Report_Open sub but when it comes to attach the controls(text boxes in the Detail Section) in the report with the recordset. It does not shows the records but a blank report. Is there something that needs to be done to attach to controls.

Thanks,

I tried it this way an it works ok.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.       Dim cmd As ADODB.Command
  3.       Set cmd = New ADODB.Command
  4.  
  5.       cmd.ActiveConnection = Application.CurrentProject.Connection
  6.       cmd.CommandType = adCmdStoredProc
  7.       cmd.CommandText = "Name of the Store Procedure"
  8.  
  9.       'Your parameters
  10.       cmd.Parameters("@startdate").Value = "1/1/1"
  11.       cmd.Parameters("@finaldate").Value = "7/7/7"
  12.  
  13.       Set Me.Recordset = cmd.Execute
  14. End Sub
I hope it helps...
Feb 22 '08 #18

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: kevin | last post by:
I have written a stored procedure (sp) that calculates the number of seats remaining for an event. I need to pass the event id to the sp. I have a combo box that lists all the events. When I choose...
4
by: Magy | last post by:
What would be the best way to execute a Oracle stored procedure that excepts several input paramters, through a web method in vb.net. What would be a good way to get to the web method, the Oracle...
1
by: Robert | last post by:
I have a form that has a table; tblProjects; as its record source. The form is filtered, however, by two combo boxes in the header. One allows the user to select a client, the other a project for...
6
by: Peter Neumaier | last post by:
Hi, I am trying to select some data through a stored procedure and would like to store the result in a local access table. Is that possible? Can somebody provide an example? Thanks&regards!...
0
by: benscribe | last post by:
Hi, I have a C# web page using a datagrid to display the results of a database query. I want to be able to pass arguments to the stored procedure inside the selectcommand asp tag, but the right...
0
by: Nitin Kshirsagar | last post by:
how to use stored procedure in ms-access through the vb6.0?
6
by: SethM | last post by:
I have a stored procedure that returns a record set. I want to functionalize this so I can have multiple presentations of the same record set. However, I can not get rs_event.open StoreProc to pass...
0
by: perdijc | last post by:
The wizard to create dataset, automatically creates a method to update table if the dataset is based on table. If i create a dataset based on sotored procedure where this is based on more one...
1
by: leec | last post by:
I need to populate an access table with the results from a SELECT stored procedure in SQL Server. 1. Is this possible? 2. If it is possible, can it all be part of the modified Stored Procedure?...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.