From MS site:

"Command text in the Command object uses the Jet SQL dialect. You can
specify row-returning queries, action queries, and table names in the
command text; however, stored procedures are not supported and should
not be specified."

Does it mean that I can't call the following Access97 query:

PARAMETERS aParam TEXT;
SELECT [aColumn] FROM [aTable] WHERE [aValue]=[aParam];

Thanks for your help.

Yann.

Yann.

What Jet supports, and you want to use, is a "stored query".
The "stored procedure" they talk about is code and/or one or more
SQL queries, compiled in the database and given a name. Access97
has no such thing, as far as I know.

Here is how I get an updatable, disconnected recordset using a
stored query with an in-parameter:

  Dim objConn As ADODB.Connection
  Dim objCommand As ADODB.Command
  Dim objRecSet As ADODB.Recordset

  Set objConn = New ADODB.Connection
  Set objCommand = New ADODB.Command
  Set objRecSet = New ADODB.Recordset

  objConn.CursorLocation = adUseClient
  objCommand.CommandText = strQueryName

  ' lngValue is the in-parameter value in this example
  objCommand.Parameters.Append objCommand.CreateParameter("aParam",
adInteger, adParamInput, ,lngValue)

  objConn.Open strConnectString
  objCommand.ActiveConnection = objConn
  objRecSet.Open objCommand, , adOpenStatic, adLockBatchOptimistic,
adCmdTable
  'Note: Important to use adCmdTable, not adCmdStoredProc

  Set objCommand.ActiveConnection = Nothing
  Set objRecSet.ActiveCommand = Nothing
  Set objRecSet.ActiveConnection = Nothing
  Set objCommand = Nothing
  If (objConn.State And adStateOpen) Then objConn.Close
  Set objConn = Nothing

Nils

Here is the code I actually used to achieve the same thing:

Public Sub getAgeBand(ByVal iAgeGroup As Integer, ageMin As Integer, agemax
As Integer)
    Dim c As New adodb.Command
    Dim r As adodb.Recordset

    Set c.ActiveConnection = m_connection
    c.CommandType = adCmdTable
    c.CommandText = "qcGetAgeRange"

    c.Parameters(0).Value = iAgeGroup
    c.Parameters(0).Size = 2

    Set r = c.Execute

    If Not r Is Nothing Then
        ageMin = r.Fields(0).Value
        agemax = r.Fields(1).Value
    End If

    Set c.ActiveConnection = Nothing
    Set c = Nothing

    If Not r Is Nothing Then
        If r.State = adodb.adStateOpen Then
            Set r = Nothing
        End If
    End If

End Sub

I was wondering, say I have 5 queries to execute one after the other, shall
I recreate each time a new Command object and a new Recordset object?
Is there some performance improvement in doing so.

I know it's cleaner not to do so but I need to gain any msec of execution.

Thanks a lot,

Yann.

Se comments inline...

Nils

Nils

Thank you immensively for your help.
I wanted to use ADO because one may need to sitch to SQL Server in the near
future.
Also, how do you achieve connection pooling using DAO and Access?

Regards,

Yann.

but you'd better ask someone in the DAO groups.
I know even less about DAO than I know about ADO...

Nils

As I said, things change fast with ADO... The fact that the ADO
(2.1) helpfile makes a point out of telling that Connection.Execute
will return a readonly recordset, while saying nothing about what
recordset the Command.Execute returns, is suggestive...

All I can say is that using ADO 2.0 (Microsoft.Jet.OLEDB.3.51)
against an Access97 database, the only way we could get hold
of an updatable recordset using parameters was to use the
command-object in the rs.Open-command. The documentation at
that time seemed to support this view.

Anyone following this thread that knows more?

Nils