SQL Parameters

[jottiwell]

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

[Clunietp]

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

    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

[jottiwell]

Brilliant!,

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

Thanks again,

John