Assign stored procedure to a report.recordset in Microsoft Access

Question

I've heard you can only do this is adp, I'm connecting directly to SQL Server, and would like to assign a stored procedure to a record and I get the error that can be done only in adp file?

I'm not linked to SQL Server, I connect directly to it with ODBC driver.

Is there any way I can assign this stored procedure to the report?

Thanks..

Answer 1

All you have to do is create a query in Access, but mark it (change it) to a pass-thougthh query.

So, the pt query can be

Exec MyStoredProcedure

Save the query. Now just use this query as the source for the report. You don't really need to write any code here. Now of course if you need to pass a value or some kind of parameter?

Well, then you can use this code:

Let's assume that the stored procedure returns data for a given invoice number.

So, your code would look like this:

dim strInvoiceNumber   as string
strInvoiceNumber = InputBox("Enter invoice number")

currentdb.QueryDefs("MyPass").sql = "EXEC StorProc " & strInvoiceNumber

' code here follows to open report
 docmd.OpenReport "MyInoice",acViewPreview

So, you can create a pass-thorough query, and type into the query any exec or command that would run/call the stored procedure. And as noted, if there are no parameters, then you don't need to write any code at all - just create the pass-though query, and use that as the source for the report.

Answer 2

Should work with a proper connection in any accdb / accde . Try the following to see if you can connect, using a fittin strconnADO. Afterwards you can assign the local rst to a report or form etc.

Public Function fctGetData(intReportNummer As Integer) As Boolean




Dim cnn As New adodb.Connection
Dim cmd As New adodb.Command
Dim rst As New adodb.Recordset   'PDO: Enthält die Empfaenger, die abzuarbeiten sind.






'PDO: Start
cnn.Open strconnADO
cmd.ActiveConnection = cnn
cmd.CommandTimeout = 180


cmd.CommandText = "dbo.q_YOUR_QUERY_sp"
cmd.CommandType = adCmdStoredProc
'cmd.CommandType = adCmdText   ' PDO: Version for PassThrough, use a SQL-Statement for CommandText


cmd.Parameters.Append cmd.CreateParameter("@Return_Value", adInteger, adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("@Report_Nummer", adSmallInt, , , intReportNummer)


rst.CursorType = adOpenStatic
rst.CursorLocation = adUseClient
rst.LockType = adLockReadOnly



Set rst = cmd.Execute()

If Err.Number <> 0 Then

    ' Do something

        GoTo Err_Handler
End If

If cmd.Parameters("@Return_Value") <> 0 Then

    ' Do something

        GoTo Err_Handler
End If

DoEvents



'PDO: Work with your recordset

If rst.EOF Then


    ' Do something

    GoTo Err_Handler
End If




        Do While Not rst.EOF

                ' Do something

        Loop '






'Cleanup


Err_Handler:
On Error Resume Next


rst.Close

Set rst = Nothing


Set cmd = Nothing
cnn.Close
Set cnn = Nothing




End Function