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

Posted on 2008-10-06
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 =

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!

Question by:katsema
  • 4
  • 4
  • 3
LVL 84

Accepted Solution

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
  MsgBox "No Data Found"
End If


Author Comment

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

LVL 10

Expert Comment

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


Author Comment

ID: 22662061
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?
LVL 10

Assisted Solution

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

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?
LVL 10

Expert Comment

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

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.

Author Closing Comment

ID: 31503648
Dear experts,

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

Author Comment

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!

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…

808 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