[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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

Posted on 2008-10-06
Medium Priority
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;"

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
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
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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 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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

656 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