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

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 =

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;"

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!

Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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
  MsgBox "No Data Found"
End If

katsemaAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

dwe761Software EngineerCommented:
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

katsemaAuthor Commented:
Dear expersts,


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


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?
dwe761Connect With a Mentor Software EngineerCommented:
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

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't use that method, but don't you have to set a flag to tell Access the query is a pass-through?
dwe761Software EngineerCommented:
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

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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.
katsemaAuthor Commented:
Dear experts,

thank you so much for advice, generocity and patience. I learned a lot!
katsemaAuthor Commented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.