I can't execute a stored procedure from Access

[omauri]

Hi. I still problems with MS-Access and SQl server 7.0. I want execute a stored procedure from Access. I declare the variables in Access, and I want to use these variables as input parameters to Stored Procedure. This is the code:

Dim param1 As ADODB.Parameter
Dim param2 As ADODB.Parameter

Dim ObjCommand As ADODB.Command
Dim Rs As ADODB.Recordset

Set ObjCommand = server.CreateObject
ObjCommand.ActiveConnection = dbconnection
ObjCommand.CommandText = "SPActCtaTerPer"
ObjCommand.CommandType = adCmdStoredProc

'assign the parameters (must be in correct order for SQL stored procedure or the order they are encountered in in the Access where clause
'PARAMETER 1
Set param1 = ObjCommand.CreateParameter("@Ids", adInteger, adParamInput)
ObjCommand.Parameters.Append param1
ObjCommand.Parameters("Ids&quot = Texto1

Set param2 = ObjCommand.CreateParameter("@Bln", adBoolean, adParamInput)
ObjCommand.Parameters.Append param2
ObjCommand.Parameters("Bln&quot = Texto3

' Execute the sql
Set Rs = ObjCommand.Execute

'release memory re command object
Set param1 = Nothing
Set param2 = Nothing
Set ObjCommand = Nothing

This is not RUN !!! Help me please!!! Thanks

Mauricio

 

[mgriffith]

first off, how do you know it's not run? what kind of query is it..it seems to be a select, so where are you using your recordset?

here's my guess: is your connection open? i know that asp recordsets operate in a fully connected state, and if the connection isn't open, then it doesn't get the data...so don't close it until you're done with your recordset (this is changed in the .net framework, where dataset's are fully disconnected)

try to call dbconnection.Open() right before you set rs = objcmd.execute
mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu

 

[cmmrfrds]

I always do my parms like so.
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = endDate

Your parm.
Set param1 = ObjCommand.CreateParameter("@Ids", adInteger, adParamInput)
ObjCommand.Parameters.Append param1
ObjCommand.Parameters("Ids&quot = Texto1

In this case Ids and @Ids appear to be different names.