How to generate a report in Access from MSSQL Server stored procedure

[mlibeson]

After a lot of trouble, I found a way to use MS Access to make my reports look pretty while using stored procedure located in MSSQL Server. Here are the steps I used without complete detail. This documentation should help you get started and may even be enough to complete your task, depending on your knowledge of Access and MSSQL.

1) Create your stored procedure on MSSQL.
EX:

use myDB;

CREATE PROCEDURE sp_myProc (@Param1 Datetime, @Param2 Datetime) AS
BEGIN
     SET NOCOUNT ON;
     IF (EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'tempdb..#myTempTable')))
     DROP TABLE #myTempTable;

     etc...

     SELECT * FROM #myTempTable;

     IF (EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'tempdb..#myTempTable')))
     DROP TABLE #myTempTable;

     SET NOCOUNT OFF;
END

2) In your MS Access DB, create a new query and select the type of query to be a pass-through query.

Open the properties window and modify the "ODBC Connect Str" by setting the value for your DSN.
EX:

ODBC;DSN=myDSN;Trusted_Connection=Yes

Write your MSSQL code to call the stored procedure just like you would on MSSQL.
EX:

EXEC sp_myProc '10/01/2006', '10/30/2006';

Save and close the query.


3) In access, create a "MAKE-TABLE" query and ADD the query from step (2) in the design view.

Note: When you add the pass-through query, it will take time for the process to display the list of fields for the query output as it runs the query in order to generate the field information.

Double click the '*' from the list in order to add all fields from the query.

Save and close query.


4)Run the query you created in step (3) so that the table exists.

Note: If you skip this step, you will have problems creating your reports from a non-existing table.


5)Create your reports in access using the design view and link it to the table that was created in step (4).


6) Create a table that holds your paramters for your stored procedure.

7) Create a form that does not use the navigation objects.

Add a control (object) for each parameter your stored procedure uses.

Add a control known as a command button.

Right click on the button while in design view and select "build event" from the drop down menu. If prompted for a type, select "code" not macro or expression builder.

A subroutine should be created for you and you just need to fill in the body. If I have a button labeled "Done" and the name of the control is "Done", then my code would look something like:

Private Sub PULL_Done_Click()
Dim qdfList As QueryDef
Dim strSql As String
Dim db As DAO.Database
Set db = CurrentDb

Set qdfList = db.QueryDefs("myPassThruQry") 'passthrough query
strSql = "EXECUTE sp_myProc '" & [Forms]![myFormName]![Param1Name] & "', '" & [Forms]![myFormName]![Param2Name] & "';" 'passing in variables
qdfList.SQL = strSql

Set qdfList = Nothing
Set db = Nothing

DoCmd.SetWarnings False
DoCmd.OpenQuery "myMakeTableQry"
DoCmd.SetWarnings True

MsgBox ("Pull Completed!")

End Sub

Add another command button to preview the report

Private Sub myCommandButtonName_Click()
On Error GoTo Err_myCommandButtonName_Click

    Dim stDocName As String

    stDocName = "myReportName"
    DoCmd.OpenReport stDocName, acPreview

Exit_myCommandButtonName_Click:
    Exit Sub

Err_myCommandButtonName_Click:
    MsgBox Err.Description
    Resume Exit_myCommandButtonName_Click
    
End Sub

Save and close form and VB code window.

That should be enough to get you started.

Good Luck!


Michael Libeson