How to call a stored procedure with paramters in SQL Server Express 2008, using code

  • Thread starter José A. Giménez [Py]
  • Start date
J

José A. Giménez [Py]

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!
 
M

Michael Gramelspacher

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.
 
S

Sylvain Lafontaine

You should be more explicit about what you mean exactly with the expression
"Receive parameters". If all you want is to catch a resultset (or a
recordset) returned by a SP, then a passhthrough query will do the job.

However, if you want to have true INPUT/OUTPUT parameters then you'll have
to go with ADO, using an ADO Command Object with its Parameters collection.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top