Access: How to get data from a SQL Server stored procedure ?

[marklerman]

I want to have an Access 97 database call a SQL Server stored procedure, pass it parameters, and retrieve the results of that procedure's final select statement. The SQL Server database is defined in ODBC.

 

[jfischer]

There are several ways to do this. Here is one example.

1) Design a new Pass Through query in Access. For the purposes of this example, I'll call it qryNwindCS_SalesByYear because I'll use the sample Northwind database converted to SQL Server. To do this, design a new query, do not select any tables (just close), and then change the query type to SQL Specific, Pass Through.

2) Open the query Properties and click on the ODBC Connect Str property to enable the builder [...] button. Click the builder button and configure your ODBC connection to SQL Server. Then, check to make sure that the Returns Records property is set to Yes, and that the Log Messages property is set to No.

3) In the query designer SQL statement text box, type the name of the stored procedure (use double quotes around the name if it contains embedded spaces) followed by a comma-separated list of parameters (use double quotes around dates, not pound signs). For example, I'll use the Northwind stored procedure named "Sales By Year" with beginning end ending date parameters:

"Sales By Year" "01/01/1996","12/31/1996"

4) Save and run your query to test it. Access should display a recordset on the screen.

5) One way to modify the parameters of the query when you use forms and reports is to change the SQL property of the query. You can do this in forms and reports and modules. Let's say that I have a form with two text boxes on it in which I can enter the Start Date and End Date for the stored procedure. I also have a command button named "cmdSalesByYear". Here's how to modify and run the Pass Through query for the stored procedure.

Private Sub cmdSalesByYear_Click()
  Dim qdf As QueryDef
  
  On Error Resume Next
  Set qdf = CurrentDb.QueryDefs("qryNWindCS_SalesByYear")
  With qdf
    .SQL = """Sales By Year"" """ _
         & Me!StartDate & """,""" _
         & Me!EndDate & """"
  End With
  Set qdf = Nothing
  DoCmd.OpenQuery "qryNwindCS_SalesByYear", acViewNormal
End Sub

To use this technique with a report, you would design the report to use the Pass Through query as its Record Source, and then modify the SQL property of the query (like in the Form example above) in the Report_Open event.

Hope this helps.

 

[marklerman]

Thank you for this response. I'm going to try it later today. Another question: if I want to do the same thing (get data from a SQL Server stored procedure) from Excel, do you have any advice ? Thanks again.

 

[jfischer]

Yes, you can use stored procedures with Excel. When you design your database query from within Excel, connect to the SQL Server and database that you want to use, but close out of any database selection. When prompted to continue using the Microsoft Query designer, choose yes. Then, open up the SQL statement entry screen (SQL toolbar button), and type "EXEC" followed by the name of the stored procedure and any parameters needed. For example:

EXEC myStoredProc '01/01/2003','12/31/2003',50000.00

 

[marklerman]

This works great. Thank you very much !!