Use SQL SP as Access Report source

  • Hello,

    I can get to the point where I can exec SQL SP in Access. However, I don't know how to set the Access report so that its source is based on the SP. Can anyone please help me? Thanks!

  • Not sure about this, but access the SP from a Query in Access (using Procedure syntax). After that, setting it as the datasource of your report should be easy.

  • Thanks for the tips, NPeeters. Figure it out!!

    For those who are interested, my solutions are as below:

    1. Create a DSN to connect to SQL Server.

    2. Create a new query in the SQL view and type: exec SPName (substitute)

    3. Change the Query to Pass-Through query (select Query --> SQL Specific --> Pass-Through)

    3. To avoid prompting the users to have to select the DSN, in my Report_Open event, I have the following code to set the connection:

    Dim strQuery As String

    Dim qryDef As QueryDef

    Dim strConn As String

    Dim strQueryName as string

    strQueryName = "queryName" (substitute)

    strConn = "ODBC;"

    strConn = strConn & "DSN=DSNName;" (substitute)

    strConn = strConn & "APP=Microsoft Access;"

    strConn = strConn & "DATABASE=db;" (substitute)

    strConn = strConn & "UID=Login;" (substitute)

    strConn = strConn & "PWD=password;" (substitute)

    strConn = strConn & "Query=" & strQueryName

    Set qryDef = CurrentDb.QueryDefs(strQueryName)

    if the SP has parameters, add the following codes, otherwise, skip it:

    qryDef.SQL = "Exec SPName @Param1=YourValue, @Param2=YourValue" (optional)

    qryDef.Connect = strConn

  • Here is an alternative function which uses the stored procedure name as a parameter and creates and ADO recordset. The recordset can then be assigned the the report recordset property.

    Public Function GetRecordsetSP(spName As String) As ADODB.Recordset

    ' basDataMgmt.GetRecordsetSP

    ' Purpose: Create an ADODB Recordset for a stored procedure

    ' Author : SCK, 07-09-2003

    ' Notes : New for ToMgmtSQL


    ' Parameters


    ' spName (String) Stored Procedure Name


    ' Returns: ADODB.Recordset


    ' Revision History


    ' 07-09-2003 sck:

    ' End Code Header block

    Dim rstRecordset As New ADODB.Recordset

    Dim cmdCommand As New ADODB.Command

    Dim lngSize As Long

    On Error GoTo HandleErr

    ' Set the command text.

    Set cmdCommand.ActiveConnection = CurrentProject.Connection

    With cmdCommand

    .CommandText = spName

    .CommandType = adCmdStoredProc


    End With

    ' Open the recordset.

    rstRecordset.Open Source:=cmdCommand, CursorType:=adOpenDynamic, LockType:=adLockOptimistic

    Set GetRecordsetSP = rstRecordset


    On Error Resume Next

    Set cmdCommand = Nothing

    Set rstRecordset = Nothing

    Exit Function


    Select Case Err.Number

    Case 10621

    MsgBox "Error: " & Err.Number & vbCrLf & "Source: " & Err.Source & vbCrLf _

    & "Description: " & Err.Description _

    & vbCrLf & "GetRecordsetSP"

    Case Else

    Call HandleTheError("", "basDataMgmt.GetRecordsetSP", Err)

    End Select

    Resume Exit_Proc


    End Function

  • I have tried that before using the Pass-Trough query. I got "Item not found in this collection" error when trying to assign the ADODB.recordset to report's recordset. Not quite sure why...

  • can you use ACCESS ADP? I supports sp on the Report record source field Natively

    * Noel

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply