Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO queries and find method

    I have used the following code to execute a parametrised query using ADO.
    The code fails if I try to use the find method with the error message 'Row set can not scroll backwards'.

    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command

    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command

    Set cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "qselC72Plus"
    Set rst = cmd.Execute(, 1, adCmdStoredProc)
    rst.MoveFirst
    rst.Find "CaseID=1" (this fails)

    etc etc

    Is there a way of executing a parameterised query and then using ADO Find method?

    Any suggetions will be appreciated.

    Thank you Mark[img]/w3timages/icons/frown.gif[/img]

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO queries and find method

    I think your code is bombing on the line

    rst.MoveFirst

    because, by default, an ADO recordset is opened as "forward only." Unless you need to run multiple searches, this need not be a problem, since the recordset pointer/cursor naturally should be positioned at the first record (or before the first record at "BOF").

    The Find method has a search direction parameter you could set:

    rst.Find ("CaseID=1",0,adSearchForward)

    Hope this helps. Plan B is to use RecordSet.Open with its appropriate parameters, rather than using the shortcut of the Command object's Execute method.

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO queries and find method

    Thanks for your reply, still no success though so it looks lke plan B. How else can a parameterised query be opened through ADO. I always seem to trip up over the best construct for this.

    Thanks Mark

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO queries and find method

    I have been doing this for a web page, so my code sample would look pretty different. Anyway, the relevant change is to skip over using the Command object and do something like this:
    <pre>Set rst = New ADODB.Recordset
    rst.Open "qselC72Plus", CurrentProject.Connection, adOpenForwardOnly, _
    adLockReadOnly, adCmdStoredProc
    </pre>


  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO queries and find method

    You said this was a parameterized query, so where are your parameters and how are you populating them? The only way I know of to execute an Access param query in ADO or DAO is to pass values to the parameters. In DAO, you get error messages if you don't. In ADO, the operation just fails silently and passes its error into the connection's errors collection.

    Here's an example of one of my routines that runs a param query using ADO and the steps through the recordset to return a concatenated string of values from a particular field. In this case, the default adOpenForwardOnly is appropriate, but in yours, you probably want adOpenDynamic:

    <pre>Public Function CallADOStoredProc( _
    ByVal SPName As String, _
    ByVal FieldName As String, _
    ParamArray Params() As Variant)
    'Created by Charlotte Foust 9/30/2000
    'Calls a saved query or stored procedure
    'and returns the values from the named
    'column as concatenated value.
    On Error GoTo Proc_err
    Dim varValue As Variant 'Holds the output--may be null
    Dim intLoop As Integer 'controls looping thru recordset
    Dim varPrmType As Variant'holds the datatype of the param

    Dim rst As ADODB.Recordset 'ADO recordset object
    Dim cmd As ADODB.Command 'ADO command object
    Dim prm As ADODB.Parameter 'holds each param object
    Dim errCurr As ADODB.Error 'holds each ADO error object

    'initialize the command object
    Set cmd = New ADODB.Command
    With cmd
    'set its properties
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText = SPName
    'loop through any passed param values
    For intLoop = LBound(Params) To UBound(Params)
    'determine the datatype of the value
    Select Case VarType(Params(intLoop))
    Case vbString
    varPrmType = adVarChar
    Case vbLong
    varPrmType = adBigInt
    Case vbDate
    varPrmType = adDate
    'if SQL Server, use adDBTimeStamp
    Case vbInteger
    varPrmType = adSmallInt
    Case vbDouble
    varPrmType = adDouble
    Case vbSingle
    varPrmType = adSingle
    Case vbBoolean
    varPrmType = adBoolean
    Case vbCurrency
    varPrmType = adCurrency
    Case vbByte
    varPrmType = adUnsignedTinyInt
    Case vbNull
    varPrmType = Null
    Case Else
    'WARNING! Not supported in ADO 2.5
    varPrmType = adVariant
    End Select 'VarType(Params(intLoop))

    If varPrmType = adVarChar Then
    ' if its text, you need to pass a length
    .Parameters.Append .CreateParameter( _
    "prm" & intLoop, varPrmType, adParamInput, _
    Len(Params(intLoop)) + 2, Params(intLoop))
    Else
    ' otherwise, skip that argument, but
    ' you have to create ALL the parameters
    .Parameters.Append .CreateParameter( _
    "prm" & intLoop, , adParamInput, , _
    Params(intLoop))

    End If 'varPrmType = adVarChar
    Next intLoop '= LBound(Params) To UBound(Params)
    End With 'cmd
    'execute the query and pass it
    'to the recordset object
    Set rst = cmd.Execute(Options:=adCmdStoredProc)
    '<<This only applies to this particular routine
    '<<because it returns a concatenated list of
    '<<column values.
    With rst
    If Not .EOF Then
    Do
    varValue = varValue & _
    Nz(.Fields(FieldName), "") & "/"
    .MoveNext
    Loop Until .EOF
    End If 'Not .EOF
    End With 'rst
    If varValue <> "" Then
    varValue = Left(varValue, Len(varValue) - 1)
    End If 'varValue <> ""
    Proc_exit:
    On Error Resume Next
    CallADOStoredProc = varValue
    rst.Close
    Set rst = Nothing
    Set prm = Nothing
    Set cmd = Nothing
    Exit Function
    Proc_err:
    If cmd.ActiveConnection.Errors.Count > 0 Then
    For Each errCurr In cmd.ActiveConnection.Errors
    MsgBox errCurr.Number & "--" & errCurr.Description
    Resume Proc_exit
    Next errCurr
    cmd.ActiveConnection.Errors.Clear
    Else
    MsgBox Err.Number & "--" & Err.Description
    Resume Proc_exit
    End If 'cmd.ActiveConnection.Errors.Count > 0
    End Function</pre>

    Charlotte

  6. #6
    New Lounger
    Join Date
    Jun 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO queries and find method

    Hi Charlotte

    There are several ways of executing parametreised queries in both ADO and DAO

    For ADO try

    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command
    rst.CursorLocation = adUseServer
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic

    Set cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "qselqueryPlus"
    Set rst = cmd.Execute(, Array(1,2,3), adCmdStoredProc) 'you can enter an array of parameters
    rst.MoveFirst

    etc ets
    ================================================== =================

    Or the Ken Ketz favourite

    this runs a parameterized action query
    Set catCurr = New ADOX.Catalog
    Set cmd = New ADODB.Command
    catCurr.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdStoredProc
    Set cmd = catCurr.Procedures("qappQtrlyCountofRefsSum").Comm and
    For Each prm In cmd.Parameters
    prm.Value = Eval(prm.Name)
    Next prm
    cmd.Execute

    etc etc
    -------------------------------------------------------------------

    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set catCurr = New ADOX.Catalog
    Set cnn = CurrentProject.Connection

    Set Rst1 = New ADODB.Recordset
    catCurr.ActiveConnection = CurrentProject.Connection


    cmd.CommandType = adCmdStoredProc
    Set cmd = catCurr.Procedures("qselReferralstoCalcTimespan"). Command
    For Each prm In cmd.Parameters
    prm.Value = Eval(prm.Name)
    Next prm
    Set Rst1 = cmd.Execute
    ------------------------------------------------------------------------
    ================================================== ======================
    In DAO its similar

    set db= currentdb()
    set qdf =db.querydefs("qrySomeQry")
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm
    set rst=qdf.openrecordset(dbopendynaset)

    Or if the prameter is on a form

    set db= currentdb()
    set qdf =db.querydefs("qrySomeQry")
    qdf.Parameters.("form1!txtmyID")= 1
    set rst=qdf.openrecordset(dbopendynaset)

    -------------------------------------------------------------------------

    I hope this is of interest

    microsoft help did come back with anything useful. Its seem as if using ADO and a query like this
    make it impossible to use the find method. Its all going backwards with microsoft!

    regards Mark

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO queries and find method

    Actually, you missed dbs.Execute for DAO queries, among others. [img]/w3timages/icons/grin.gif[/img]

    With ADO, there many more ways to execute a query, paramtertized or otherwise, than you listed. The problem is choosing the right way for the situation. ADO 2.5 and earlier doesn't allow you to use named parameters, so you have to know what order the params come in and what they are to keep the query from failing silently. I haven't tried using the catalog object because it is simple to execute a query using either the connection or the command object and doesn't require an additional reference to the ADOX library.

    The code I posted is intended to handle queries it knows nothing about, rather than being customized for a particular query as in your examples. I think something got left out of Ken's favorite, though. There's no dim statement for the prm object, and it's one of those that exists in both object models and will bite you if you have both DAO and ADO references set.
    Charlotte

  8. #8
    New Lounger
    Join Date
    Jun 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO queries and find method

    I agree with you Charlotte there are many ways to execute queries using ADO. It can be confusing and knowing if one is using the optimal method can be a problem. In this case I have been unable to establish a way of using the Find method on such queries, and Microsoft have not helped either, so I don't know if its possible in this situation.
    Regarding my lazyness I left out the Dim statements as being understood. As to the Execute statement I believe the code works without, or at least I hope so as I have used this in a project and there have not been any complaints, I am fairly sure the syntax works but I will check it when I get a chance.
    Thanks for your help.
    Mark

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO queries and find method

    Is it only with parameter queries that you're having problems? I haven't had any problems with Find on ADO recordsets other than its unidirectional nature. I generally use code like this successfully, although I don't think I've tried it with a parameter query:

    <pre> With mrst
    'start searching from the next record forward
    .Find strCriteria, 1
    If .BOF Or .EOF Then
    'if not found, try searching backward
    .Find strCriteria, , adSearchBackward
    End If
    If .BOF Or .EOF Then
    'if not found, notify the user
    MsgBox "No matching record found"
    Else
    'if found, display the record
    ...
    End If</pre>

    Charlotte

  10. #10
    New Lounger
    Join Date
    Jun 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO queries and find method

    Thanks for your reply. I have not run into this problem with recordsets created from tables or non parameterised queries either.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO queries and find method

    Is it possible that you're trying to Find something that is outside the recordset returned by the parameters?
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •