Hi, i have a stored procedure in SQL Server Express 2008 and i need
send/receive parameters from stored procedure.
I dont know how do it using code in Access 2007.
Help please...
Thanks!
Here is a function which was posted by mgf in an Access newsgroup:
Function RunPassThruQuery(strSQL As String, strConnect As String, _
fReturn As Boolean) As DAO.Recordset
' Purpose:
' Run a temporary query with the indicated SQL statement, for
' the indicated Connection string. If fReturn = True return a
' DAO.Recordset.
' In:
' strSQL The statement to run
' strConnect The ODBC connect string
' fReturn Indicates whether to return a recordset
' Out:
' DAO.Recordset - Only if fReturn = True
' Errors, if any
' Created:
' mgf 27apr2001
' Modified:
'
On Error GoTo err_
Dim db As DAO.Database
Set db = CurrentDb
' Create a temporary QueryDef object to run statement.
Dim qd As QueryDef
Set qd = db.CreateQueryDef("")
With qd
.Connect = strConnect
.SQL = strSQL
.ReturnsRecords = fReturn
If fReturn Then
Set RunPassThruQuery = .OpenRecordset()
Else
.Execute
End If
End With
exit_:
On Error Resume Next
Set qd = Nothing
Set db = Nothing
Exit Function
err_:
Dim strError As String
Dim lngError As Long
' First get the ODBC errors
strError = "ODBC errors: " & vbCrLf
Dim e As Variant
For Each e In DBEngine.Errors
strError = strError & e & vbCrLf
Next e
' Then the Access error
lngError = Err.Number
strError = strError & vbCrLf & "Access error: " & vbCrLf
strError = strError & Err.Description
' Clean up
On Error Resume Next
Set qd = Nothing
Set db = Nothing
' Send the error back to the calling routine
On Error GoTo 0
Err.Raise lngError, , strError
End Function
Here is part of the code I use:
strSQL = "EXEC DaysDiffBirthBapt '" & Me.txtStartDate & _
"','" & Me.txtStopDate & "','" & Me.txtChurchName & "'"
' use the connect string from one of the linked tables
strConnect = CurrentDb.TableDefs("dbo_qryBaptisms").Connect
' call the function which returns a recordset
Set rs = RunPassThruQuery(strSQL, strConnect, True)
Maybe this is of some help.