Solved

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

Posted on 2008-10-06
11
1,753 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
  • 4
  • 4
  • 3
11 Comments
 
LVL 84

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 84
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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 84
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 84
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now