Use Access to Call a stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Use Access to Call a stored Procedure

Author

Topic

MikeBeck
Starting Member

1 Post

Posted - 2013-06-05 : 22:41:21

See code below.

This runs, but I would like to call the same SP 4 times passing new parameters each time. Do I have to drop the parameters and add new one? Can I change the value in a existing parameter? Do I have to drop the cmd and write a new one. I would like to use the most efficient method.

Thanks, Mike

Dim intTest As Integer
Dim cmd As New ADODB.Command
Dim cnn As New ADODB.Connection
Dim prmID As New ADODB.Parameter

With cnn
.Provider = "SQLOLEDB"
.ConnectionString = "data source=XXXXXXXX-PC;initial catalog=ProdDB;Trusted_Connection=Yes"
.Open
End With

With prmID
.Name = "Return"
.Type = adInteger
.Direction = adParamReturnValue
End With

With cmd
.Parameters.Append prmID

.Parameters.Append .CreateParameter("@Session", adInteger, adParamInput, , 3)
.Parameters.Append .CreateParameter("@Vessel", adInteger, adParamInput, , 3)
.Parameters.Append .CreateParameter("@Position", adVarChar, adParamInput, 5, "Lead")
.Parameters.Append .CreateParameter("@Pressure", adVarChar, adParamInput, 5, "H")

.ActiveConnection = cnn
.CommandText = "usp_CalcPart"
.CommandType = adCmdStoredProc
.Execute
intTest = .Parameters("Return")

End With

cnn.Close

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-06-06 : 10:38:16

Make it a function that accepts 4 arguments: intSession, intVessel, strPosition, strPressure.

then these lines will look like:


.Parameters.Append .CreateParameter("@Session", adInteger, adParamInput, , cint(intSession))
.Parameters.Append .CreateParameter("@Vessel", adInteger, adParamInput, , cint(intVessel))
.Parameters.Append .CreateParameter("@Position", adVarChar, adParamInput, 5, cstr(strPosition))
.Parameters.Append .CreateParameter("@Pressure", adVarChar, adParamInput, 5, cstr(strPressure))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources