EXCEUTE a Stored Procedure from ACCESS front-end

[Steve5]

Hello all,
I am looking for a way to execute a Stored Procedure on SQL server 2000 from my ACCESS 2003 front end.

(Using a button)

Does anyone have an example Access DB or code available that could help me make this happen? I would really appreciate!!!

Kind Regards
Steve~

 

[pbaldy]

I cut a bunch of unrelated stuff out of this, but hopefully you get the idea. Obviously I use a global connection, so adjust accordingly.

  Dim cmd                As ADODB.Command

  Call EstablishConnection
  Set cmd = New ADODB.Command
  With cmd
    .ActiveConnection = objConn
    .CommandText = "procAbraMover"
    .CommandType = adCmdStoredProc

    .Execute

  End With
  Call ReleaseConnection

 

[FoFa]

I usually use a passthrough query and do
EXEC Procname 'StrParm1','Strparm2',NumParm1
as an example

 

[Steve5]

Looks like i found how (thank-you)

I figured everyone on this sight have been so helpful in the past. It is my turn to add something to the group. Here is my code for calling a stored procedure from ACCESS.

'TO call the function is as follows:
'--------------------------------------------------------
Private Sub Command2_Click()

Dim clsSP As Class1
Set clsSP = New Class1

clsSP.spDEV ("App_Table1")

Set clsSP = Nothing
End Sub

'-----------------------------------------------------------

Public Function spDEV(StoredProcedure As String)

Dim adoCn As adoDb.Connection
Dim adoRs As adoDb.Recordset
Dim adoCm As adoDb.Command
Dim strSQL As String

Set adoCn = New adoDb.Connection
With adoCn
.ConnectionString = "Provider=MSDASQL;Driver={SQL Server};Server=USSDIVPC2DEV;" & _
"Database=MyDatabase;Uid=sa;Pwd=12345"
.CursorLocation = adUseServer
.Open
End With
Set adoCm = New adoDb.Command
With adoCm
Set .ActiveConnection = adoCn
.CommandType = adCmdStoredProc
.CommandText = StoredProcedure
.Execute
End With

MsgBox "SP Complete...", vbOKOnly

adoCn.Close
Set adoCn = Nothing
Set adoRs = Nothing

End Function

Thank-you Enjoy

 

[jaji03]

Call Stored Procedure from MS Access 2003

I shall contribute as well. I'm not a big programmer, but this is what I got and it works for me ......

I create a separate module like this:

Option Compare Database
Public ConnSQL As New ADODB.Connection

Public Sub Conn_SQLServer()
Dim strSQL As String

On Error GoTo Error_Handler

If ConnSQL.State = 1 Then Exit Sub 'if connection already on, then exit

With ConnSQL
.ConnectionString = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=Server_Name;DATABASE=DB_Name;TRUSTED_CONNECTION=Yes;"
.CursorLocation = adUseServer
.Open
End With


Exit Sub
Error_Handler:

MsgBox "Unable to connect to SQL Server." & "Error " & Err.Number & " - " & Err.Description & ", " & Err.Source, vbExclamation

End Sub

Then I call it where ever I need an Sproc ran....

Public Sub RunMonthly()
Dim recSQL As New ADODB.Recordset
Dim strSQL As String

Call Conn_SQLServer

On Error GoTo Err_Handler

strSQL = "EXEC S_Lookup_Locus '{TDate}'"
strSQL = Replace(strSQL, "{TDate}", Format(dteBeginDate, "Short date"))

ConnSQL.CommandTimeout = 0 'Prevents timeout error
Set recSQL = ConnSQL.Execute(strSQL)

If recSQL.State = 1 Then recSQL.Close

Exit Sub

Err_Handler:
MsgBox Err.Number & " " & Err.Description & " " & Err.Source
Exit Sub