SQL Parameters

Hello,

I'm trying to process an sql command within Access 2000 VBA, and get it to return a parameter, i.e. ( simple example )

Dim pricon As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim outcount As Parameter
pricon.ConnectionString =CurrentProject.BaseConnectionString
pricon.CursorLocation = adUseClient
pricon.Open
cmd.ActiveConnection = pricon
cmd.CommandType = adCmdText
cmd.CommandText = "Select @outcount = count(*) from [tablename]"
Set outcount = cmd.CreateParameter("outcount", adInteger, adParamOutput)
cmd.Parameters.Append outcount
cmd.Execute

MsgBox outp.Value

This refuses to work as it says 'Must define parameter 'outcount'. It'll work if I call a stored proc with the parameter declared at the top. i.e.

Create [procname]

@outcount integer output

as

select @outcount = count(*) from [tablename]

but I don't want to have to define a stored procedure every time I just want to ( for example ) count something. I'm trying to get this to work because I don't want to have to put everything into client side recordsets ( have to be client side because of -1 .recordcount error ), as that slows Access runtime massively and eats resources.

Any help at all would be wonderful.

Thanks

John

this works fine for me, this allows me to get a recordcount without resorting to commands & parameters

Code:

---

    Dim objCN As ADODB.Connection
    Dim objRS As ADODB.Recordset
   
    Set objCN = New ADODB.Connection
    Set objRS = New ADODB.Recordset
    'conn to SQL server
    objCN.Open "Provider=SQLOLEDB;Data Source=SRV;Integrated Security=SSPI;Database=Northwind"
   
    'get data using static, client side cursor
    objRS.CursorLocation = adUseClient
    objRS.Open "Select count(*) from customers", objCN, adOpenStatic, adLockReadOnly, adCmdText

    MsgBox objRS(0)    'gives us the count, not all of the data

---

hope this helps

Tom

Brilliant!,

Thanks very much, that's just what I've been looking for, simple once you've seen it!

Thanks again,

John