Problems Executing SavedQuery in Code

[RMittelman]

Hi,



I am trying to execute an Access 2007 saved query from my VBA code, and am encountering some weirdness. The function below should return a recordset from the specified saved query. I have hard-coded the query name and the parameters for simplicity.

Using ADOX, I can see what the query parameters are, and my function loads each parameter, including trimming the length of string-type parameters if needed.

The proper record IS being returned in the recordset. I have the particular fields, which I can access by:
Dim rs As ADODB.Recordset
Set rs=MyTest
? rs.Fields(0)
? rs.Fields(fieldName)
, etc.

The weirdness is that I can't get the AbsolutePosition or RecordCount properties after invoking the function. They both return -1. My calling code wants to test for a valid result by saying
If rs.AbsolutePosition = 1 Then
.....

But, this isn't working. I've tried playing with properties of the recordset object, the command object & the catalog object, to no avail.

Any ideas?
Thanks...

Friend Function myTest() As ADODB.RecordSet

    Dim cmd             As New ADODB.Command
    Dim rs              As ADODB.RecordSet
    Dim cat             As New ADOX.Catalog
    Dim parmType        As ADODB.DataTypeEnum
    Dim parmsSupplied   As Integer
    Dim parmVal         As Variant
    Dim parmNo          As Integer
    Dim tries           As Integer
    Dim params()        As Variant
    Const my_query      As String = "GetPellAmountForProfile"
    
    On Error GoTo err_handler
    
    params = Array(1519, 500)
    cat.ActiveConnection = CurrentProject.Connection
    tries = 0
    
find_cmd:

    'If query not found as an ADOX Procedure, try finding it as an ADOX View.
    If tries = 0 Then
        Set cmd = cat.Procedures(my_query).Command
    Else
        Set cmd = cat.Views(my_query).Command
    End If
    
    parmsSupplied = UBound(params) - LBound(params) + 1
    If parmsSupplied = cmd.Parameters.Count Then
        For parmNo = 0 To cmd.Parameters.Count - 1
            With cmd.Parameters(parmNo)
                parmVal = params(parmNo)
                Select Case .Type
                    Case adBSTR, adChar, adLongVarChar, adLongVarWChar, adVarChar, adVarWChar, adWChar
                        parmVal = Left$(parmVal, .Size - 1)
                End Select
                .Value = parmVal
            End With
        Next
        Set rs = cmd.Execute
        Set myTest = rs
        Set cmd = Nothing
        Set rs = Nothing
        Set cat = Nothing
        
    Else
        myError = "Wrong number of parameter values supplied. Expected " & CStr(cmd.Parameters.Count)
        
    End If 'Right number of parameters supplied.

exit_handler:
    On Error Resume Next
    Set cat = Nothing
    On Error GoTo 0
    Exit Function
    
err_handler:
    
    'Could be a view, not a procedure.
    If Err.Number = 3265 Then
        If tries = 0 Then
            tries = 1
            Resume find_cmd
        Else
            update_query_params = False
            Resume exit_handler
        End If
    Else
        update_query_params = False
        myError = Err.Description
        Resume exit_handler
    End If

End Function

[ajetrumpet]

those properties are probably returning -1 because you havent opened the recordset with:

rs.movelast
rs.movefirst

[RMittelman]

Thanks, Adam, but no cigar. I already tried that. When I execute a move first, I get the old "Either BOF or EOF is true..." error. If I was opening a recordset from the connection object, I would open the connection and set it's cursor location to client before opening the recordset. Since I'm opening the command from the ADOX catalog instead, there's nothing I can set.

It still works, but I can't test the recordset for "okayness" before accessing the fields.

Any other ideas?

Thanks...

[ajetrumpet]

set it's cursor location to client before opening the recordset.

I have absolutely NO idea what you mean by that. I don't use ADOX often. I don't even have any experience with .command, so i'm not sure what to tell ya.

all I know is that -1 for those properties usually appears because you haven't opened the actual RECORDS in a recordset, just opened the recordset object. that applies to both ADO and DAO.

[RMittelman]

Thanks, Adam. And yes, I know you are correct about that. Normally, you open a recordset object using a connection. A -1 means an empty recordset. In my case, I know it is not empty, because even though trying to get the AbsolutePosition property or the RecordCount property returns -1, I can still get the field values themselves. I can't issue a MoveFirst or MoveNext however, without getting an error.

This is fine if I only queried a 1-record dataset, but there is not a way to verify that the query worked, other than asking for the field values.

As far as UseClient goes, it is a setting that you apply to either the connection object or the recordset object before actually opening the recordset. You apply it to the CursorLocation property. It can either be UseServer or UseClient, and tells where the data actually resides when you have obtained it. Usually, if you use the server location, you don't have access to the RecordCount property.

ADOX is a set of extensions to ADO, which give you a bunch more information. For example, If you open a query object in ADO or DAO, you don't know what the parameters are, although you CAN add them and execute the query. Using ADOX lets you actually see what the parameters of the query are, their names, types and sizes. It makes it easier to see what to fill in before executing the query.

Unfortunately, it doesn't seem to give me a recordcount or an absolute position, so I can't easily verify the query worked.

Thanks anyway, and if anybody else has an idea, please let me know...