Call SQL Stored Procedure from Access 97 2

[alexyo7]

I'm trying to call a SQL Server stored procedure from an Access 97 database. I tried looking through the old threads and found a few items related to stored procedures, but nothing that clearly answered my question.

The Stored Procedure is called ProcCreateCase, and it is expecting to paramaters: Param1, Param2.

Could someone please show me the code required to call that stored procedure with the two paramaters when the user pushes the command button?

Thanks in advance!

Alex

 

[cmmrfrds]

What are you going to do with the data returned? Does the the result need to be bound to a Form? Is the result returned to a recordset that is processed internally - unbound?

 

[alexyo7]

I mainly just need to pass the paramaters and run the procedure, so that a bunch of work can take place on the SQL server. I'm not really expecting anything back. Maybe a success/failure flag at most.

 

[cmmrfrds]

Use the ADO Command. I am doing this from memory at work, but have many examples at home. If this does not work I will check back when I get home. If you encounter an error be explicit with the description.


'-- Reference Library
'-- Microsoft ActiveX data objects 2.6 library needed for ADO
Dim cn As New adodb.Connection
Dim rs As adodb.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=pubs;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

Dim cmd As New adodb.Command
Dim Parm1 as adodb.Parameter, parm2 as adodb.Parameter

Set parm1 = cmd.CreateParameter("parm1", _ adInteger, adParamInput)
Set parm2 = cmd.CreateParameter("parm2", _ adVarchar, adParamInput, 20)

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "ProcCreateCase"
cmd.ActiveConnection = cn
cmd.Execute

cn.Close
Set cn = Nothing

 

[cmmrfrds]

I forgot the part about adding the parm to the Parameters collection and assigning the values.

Set parm1 = cmd.CreateParameter("parm1", _ adInteger, adParamInput)
cmd.Parameters.Append parm1
parm1.Value = 77

Set parm2 = cmd.CreateParameter("parm2", _ adVarchar, adParamInput, 20)
cmd.Parameters.Append parm2
parm2.Value = "abc"

 

[alexyo7]

I got it to work. Thanks for your help!

 

[cmmrfrds]

You are welcome, glad it worked.