Execute SQL stored procedure from VBA 1

[Webkins]

Is it possible to trigger a SQL stored procedure from VBA without using an ODBC connection ?

Software involved laptop: Access 2003, Windows XP,
Software involved server: SQL 2000, Windows 2003 Server.

I would like to write the VBA code and trigger a stored procedure from my laptop to the server in Access 2003. Is this possible without creating a ODBC connection in the control panel ?

Now I have a Access 2003 query saved which has all the ODBC connection data saved within and code that calls the query to execute the stored procedure within the query. I would like to remove the query and do this directly from VBA. Possible ? Thank you.

 

[PWise]

Paste This code in a module

call ExecuteAdo("yourSpname",4,0,pram1,pram2,....)

Option Compare Database
Option Explicit
Public CnnAlt As New ADODB.Connection
Public Cnn As New ADODB.Connection
Dim cmd As New ADODB.Command


Function InitializeAdo()
If Cnn.State = adStateClosed Then
    Cnn.ConnectionTimeout = 0
    Cnn.Open [COLOR=red]Type your Connection string [/color]
End If
End Function


Function ExecuteAdo(AdoString As String, adoCommandType As Integer, ParamArray AdoPrams())
'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim a As Integer
InitializeAdo
cmd.CommandText = AdoString
Set cmd.ActiveConnection = Cnn
cmd.CommandType = adoCommandType
cmd.CommandTimeout = 0
For Prams = 0 To UBound(AdoPrams)
    cmd.Parameters(Prams) = AdoPrams(Prams)
Next Prams
cmd.Execute a
ExecuteAdo = a
End Function

Function ExecuteAdoRS(AdoString As String, adoCommandType As Integer, ParamArray AdoPrams()) As ADODB.Recordset

'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim a As Integer
InitializeAdo
cmd.CommandText = AdoString
Set cmd.ActiveConnection = Cnn
cmd.CommandType = adoCommandType
cmd.CommandTimeout = 0
For Prams = 0 To UBound(AdoPrams)
     cmd.Parameters(Prams) = AdoPrams(Prams)
     'Debug.Print cmd.Parameters.Item(Prams).Name, Prams, cmd.Parameters.Item(Prams).Value
Next Prams
Set ExecuteAdoRS = cmd.Execute(a)
If adoCommandType = 4 Then AdoPrams(0) = cmd(0)
End Function
Function ExecuteAdoOutput(AdoString As String, adoCommandType As Integer, ParamArray AdoPrams()) As ADODB.Command
'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim a As Integer
InitializeAdo
cmd.CommandText = AdoString
Set cmd.ActiveConnection = Cnn
cmd.CommandType = adoCommandType
cmd.CommandTimeout = 0
For Prams = 0 To UBound(AdoPrams)
    cmd.Parameters(Prams) = AdoPrams(Prams)
Next Prams
cmd.Execute
Set ExecuteAdoOutput = cmd
End Function

Function ExecuteAdoOutputRS(AdoString As String, Returns() As Variant, adoCommandType As Integer, ParamArray AdoPrams()) As ADODB.Recordset
'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim a As Integer
InitializeAdo
cmd.CommandText = AdoString
Set cmd.ActiveConnection = Cnn
cmd.CommandType = adoCommandType
cmd.CommandTimeout = 0
For Prams = 0 To UBound(AdoPrams)
    cmd.Parameters(Prams) = AdoPrams(Prams)
Next Prams
Set ExecuteAdoOutputRS = cmd.Execute
ReDim Returns(UBound(AdoPrams))
For Prams = 0 To UBound(AdoPrams)
    Returns(Prams) = cmd(Prams)
Next Prams
End Function