Solved

How to get SQL stored procedure results to be a record source for Access report

Posted on 2008-10-06
11
1,954 Views
Last Modified: 2013-11-28
I am trying to run run SP in onOpen event of the report.
Simple way:
Me.RecordSource = "exec dbo.spmonthProc @start_date = '" & sdate & "' , @end_date = '" & edate & "'"

gives me:
Invalid SQL statement: expected  'DELETE", "INSERT", "PROCEDURE", "SELECT" OR "UPDATE"
which is understandable because I need to connect to the server

More sophisticated approach:


Dim rsS As DAO.Recordset
Dim sdate As Date
Dim edate As Date
Dim dbo As Database
Dim sConn As String
Dim cmd As Command
    sdate = #8/1/2008#
    edate = #8/5/2008#
    Set dbo = CurrentDb
    sConn = dbo.TableDefs("dbo_patient").Connect
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = sConn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "mspMonthProc"
    ' supply first parameter
    cmd.Parameters.Append cmd.CreateParameter("start_date", addatetime, adParamInput, 8, sdate)
    ' supply second parameter
    cmd.Parameters.Append cmd.CreateParameter("end_date", addatetime, adParamInput, , edate)
   
    'Run Stored Procedure
    Set rsS = cmd.Execute
    Me.RecordSource = rsS.name

Gives me an error at :
cmd.ActiveConnection = sConn

gives me an error:
Data source name not found and no default driver specified

Whatever I do: spell connection string out like:
sconn = "ODBC;DRIVER={SQL Server};SERVER=LENKA;DATABASE=scottsdale;UID=sa;PWD=mypassword;"

or
sconn = "ODBC;DSN=dentalSQL;APP=Microsoft Office 2003;WSID=LENKA;DATABASE=scottsdale"
I am still getting the error.

And I think I am not the first one to run a report based on SP run results

Thank you in advance!

0
Comment
Question by:katsema
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 22654947
Remove the ODBC in front of the connect string:

oConn.Open "DRIVER={SQL Server};SERVER=LENKA;DATABASE=scottsdale;UID=sa;PWD=mypassword"

What version of Access? 2002 introduced the Recordset property for Forms and Reports. I'd do this:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "DRIVER={SQL Server};SERVER=LENKA;DATABASE=scottsdale;UID=sa;PWD=mypassword"

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "EXEC mspMonthProc @start_date='8/1/2008', @end_date='8/5/2008'", cn

If Not (rs.EOF and rs.BOF) Then
  Set Reports("YourReport").Recordset =rs
Else
  MsgBox "No Data Found"
End If

0
 

Author Comment

by:katsema
ID: 22655280
Thank you,

Code is nice and clean but I am getting:
2593: This feature is not available im an MDB
when trying to execute

Set Reports("YourReport").Recordset =rs

Is there any way around? I have Access 2003 and SQL server 2000, under Windows XP
0
 
LVL 85
ID: 22657711
Sorry ... thought this was an ADP file. You can't use that property in a standard MDB file. I've used the temporary table routine for this - open your Stored Proc, dump everything to a local temporary table, and use that table to build your report. It sounds like a lot of overhead, but I've found to be as fast (if not faster) than dealing with the construct above in my ADP files.



0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 10

Expert Comment

by:dwe761
ID: 22659462
To make a report based on a stored proc, one approach is:
1) Create a pass-through query (in design view, Query--> SQL Specific --> Pass-through)  or you can use the code below.  If you don't use the code for creating the query, then just update the SQL for the existing query so you can insert your parameters as I've shown.
2) For your pass-through query, get the connection string from a table you've already got attached to your data source.  If you don't have one yet, you'll have to build it.

3) Build your pass-through, complete with any parameters prior to pasting it into the pass-through query itself.  Because whatever you paste in the query must be a valid SQL statement for SQL Server.
4) Set your report recordsource = "Pass-through query name"

   sQry = "MyPassThruQry"
   sConnect = CurrentDb.TableDefs("tblAlreadyAttached").Connect
   CurrentDb.CreateQueryDef sQry, "exec dbo.spmonthProc @start_date = '" & sdate & "' , @end_date = '" & edate & "'"
   CurrentDb.QueryDefs(sQry).Connect = sConnect
   CurrentDb.QueryDefs(sQry).ODBCTimeout = 0
   Me.RecordSource = sQry 

Open in new window

0
 

Author Comment

by:katsema
ID: 22662061
Dear expersts,

LSMConsulting:

I dumped everything to a tremporary table - worked fast (much faster than executing stored procedure) and gives perfect results.

dwe761.

your method gives an error:
Invalid SQL statement: expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE
when trying to execute:
CurrentDb.CreateQueryDef sQry, "exec dbo.spmonthProc @start_date = '" & sdate & "' , @end_date = '" & edate & "'"

Actually, I'd like to learn many ways of doing that. Can you recommend any good books on Access-SQL, especially SQL server?
0
 
LVL 10

Assisted Solution

by:dwe761
dwe761 earned 125 total points
ID: 22662275
I use this technique all the time so there must be something wrong with the SQL statement.  I just pasted your SQL from your snippet so not sure where sdate and edate were coming from?  Are these controls on a form or were they parameters?  you could separate the two lines of code to find out where it's breaking down, set a breakpoint before changing the query, and adjust the code accordingly until you've got a valid SQL statement that actually executes on the SQL side.
As to books, I can recommend:
Microsoft Access Developer's Guide to SQL Server (Sams White Book) (Paperback)
by Andy Baron, Mary Chipman
The Guru's Guide to Transact-SQL (Paperback)
by Ken Henderson

   'Assuming you have already created query sQry as a pass-through query
   sSQL = "exec dbo.spmonthProc @start_date = '" & sdate & "' , @end_date = '" & edate & "'"
   CurrentDb.QueryDefs(sQry).SQL = sSQL
   Docmd.OpenQuery sQry

Open in new window

0
 
LVL 85
ID: 22663327
I don't use that method, but don't you have to set a flag to tell Access the query is a pass-through?
0
 
LVL 10

Expert Comment

by:dwe761
ID: 22663852
Sorry, my mistake in my snippet was that if you are creating the query in code using this technique, you have to first provide a valid SQL statement that Access can handle even if it's a dummy table name that does not exist.  After the query is created, then give it a connection string and the SQL string you want to pass to SQL Server.

As long as the Query's Connection property has a valid connect string, it knows that it is a pass-through.

   CurrentDb.CreateQueryDef sQry, "SELECT * FROM tblDUMMY;"
   If Currentdb.QueryDefs(sQry).Name = sQry Then 'Error possible
      CurrentDb.QueryDefs(sQry).Connect = sConnect
      CurrentDb.QueryDefs(sQry).ODBCTimeout = 0
      CurrentDb.QueryDefs(sQry).SQL = sSQL
      Docmd.OpenQuery sQry
   End If

Open in new window

0
 
LVL 85
ID: 22663865
<As long as the Query's Connection property has a valid connect string, it knows that it is a pass-through. >

That's what I thought, but I wasn't sure ... I don't use stored querys with my SQL Server backed projects, so wasn't sure.
0
 

Author Closing Comment

by:katsema
ID: 31503648
Dear experts,

thank you so much for advice, generocity and patience. I learned a lot!
0
 

Author Comment

by:katsema
ID: 22665014
Dear DWE761,

My first mistake was that my query was not a pass through query, just a regular one. As soon as I changed it, everything worked beautifully. Thank you, I learned a lot.

And thank you for book recommendations also!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question