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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy