Question: Access and SQL Store Procedure

[ndg]

Do do I go about in executing a store procedure in the SQL server through Access form?
Do I need to create a recordset to do this?
Or can I just call it by the Execute method.
Please explain to me in detail.
Any help is appreciated.

Thanks a lot.

 

[Psychpt]

what you need is a pass-through query.

HTH

PsychPt@Hotmail.com

 

[jimmythegeek]

Here is an example of calling a strored procedure using an ADO Connection. Use it as a reference, Information would need to be changed accordingly.

==========
Public Const ADOConnect As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DATABASENAMEHERE;Data Source=SERVERNAMEHERE"

Private Sub GetStoredProcedure()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim adoCN As ADODB.Connection

Set adoCN = New ADODB.Connection ' Normally on initial open of application
adoCN.Open (ADOConnect) ' Normally on initial open of application
Set cmd = New ADODB.Command

With cmd
Set .ActiveConnection = adoCN
.CommandType = adCmdStoredProc
.CommandText = "StoredProcNameHere"
' Use the following two lines if you have a parameter to pass
'Set paritem = .CreateParameter("Keyword", adVarChar, adParamInput, 255, Me.txtValue)
'.Parameters.Append paritem
End With

Set rs = New ADODB.Recordset
rs.Open cmd, , adOpenStatic

' Do whatever you want here .......

Set rs = Nothing
Set cmd = Nothing
Set adoCN = Nothing
End Sub
========== Jim Lunde
compugeeks@hotmail.com
Custom Application Development