October 13, 2003 at 6:52 pm
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!
October 14, 2003 at 1:56 am
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.
October 14, 2003 at 5:25 pm
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
October 16, 2003 at 10:18 am
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
.Execute
End With
' Open the recordset.
rstRecordset.Open Source:=cmdCommand, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
Set GetRecordsetSP = rstRecordset
Exit_Proc:
On Error Resume Next
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Exit Function
HandleErr:
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
Resume
End Function
October 16, 2003 at 6:08 pm
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...
October 24, 2003 at 12:41 pm
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