How can I call SQL store procedure with VBA ?

  • Thread starter Thread starter Mac
  • Start date Start date
Here's the little function I use to do this using DAO. It would need
modification for your use, but you can get the idea from the below. Also, as
written, this doesn't support returning records or values -- it just executes
SQL. For example, to execute a stored procedure that takes two arguments,
you might set strSQL to something like "EXEC spYourSP 1, 2."

David

Function ExecuteSPT(strSQL As String) As Boolean
On Error GoTo Err_ExecuteSPT

' execute action pass-through query or stored procedure
Dim qdf As QueryDef

' ensure the connection is specified (set in frmSplash and copied here
for good measure)
If gSQLServerODBCConnection = "" Then
gSQLServerODBCConnection = CurrentDb.TableDefs("tblNothing").Connect
End If

' execute the SQL
Set qdf = CurrentDb.CreateQueryDef("")
qdf.ReturnsRecords = False
qdf.Connect = gSQLServerODBCConnection
qdf.ODBCTimeout = 120 ' two minutes to time-out
qdf.SQL = strSQL
qdf.Execute
qdf.Close
Set qdf = Nothing

' return function success
ExecuteSPT = True

Exit_ExecuteSPT:
Exit Function

Err_ExecuteSPT:
ExecuteSPT = False
Set qdf = Nothing
If Err.Number = 3146 Then Resume Exit_ExecuteSPT ' ODBC call
failed; don't log as code should handle failures
ErrorHandler "modGlobal", "ExecuteSPT", Err.Number, Err.Description
Resume Exit_ExecuteSPT

End Function
 
Back
Top