Very Computer

I've got an interesting scenario here. The following code works
perfectly against Oracle 7.3 database using DAO. (It's outdated code
and we need to migrate this code to Oracle 8i)

The query is stored in the sring known as S. The query returns results
on the final command line:

    Set SelectSQL = qdf.OpenRecordset(dbOpenSnapshot)

This returns SelectSQL back to the original source as a recordset.
Yet, it works perfectly on Oracle 7.3 database using ODBC Microsoft
Driver 2.5 for Oracle, Oracle Driver 7.3 and 8.0 driver.

There are problems with other drivers. Microsoft ODBC 2.5 driver for
Oracle seems to work best but in checking out the recordcount -
SelectSQL.RecordCount returns a value of -1.

So I'm asking - are querydefs incompatible with 8i? I made the
recommendation of overhauling the code to ADO (a lot of code)

Any suggestions how we can 'fix' this as a crutch in order to make
this a smooth but temp solution to 8i? Thanks! See code below:

Function SelectSQL(s As String) As Recordset
'--------------------------------------------------------------------
'   Purpose:     Runs as SELECT SQL query.
'
'   Returns:     A recordset containing the results of the query.
'--------------------------------------------------------------------
    On Error GoTo SelectSqlError
    Dim qdf As QueryDef
    Dim i As Long

    Set qdf = con.CreateQueryDef("")
    qdf.SQL = s
    qdf.MaxRecords = 0

    i = con.CreateQueryDef("", "SELECT COUNT(*) " _
        & Right$(s, Len(s) - (InStr(1, UCase$(s), "FROM") - 1))) _
        .OpenRecordset(dbOpenSnapshot).Fields(0)

    If i < 1 Then i = 1
    qdf.CacheSize = i

    Set SelectSQL = qdf.OpenRecordset(dbOpenSnapshot)

    Exit Function
'--------------------------------------

SelectSqlError:
      MsgBox "Function SelectSQL is Unable to run query: " &
LCase$(s), _
        vbCritical, Err.Number & " - " & Err.Description
End Function

Actually, this behavior is by design.  The recordset object doesn't 'know'
how many records it has.  The -1 means 'True' as in it has records.  You
have to invoke the .MoveLast then .MoveFirst methods to get an actual
recordcount.  This may/may not work depending on the CursorType when the
Recordset was opened.
  I would suggest writing a proc that with an output parameter that returns
the number of records.  It'll be a lot easier for you.
  Frank.

Quote:> I've got an interesting scenario here. The following code works
> perfectly against Oracle 7.3 database using DAO. (It's outdated code
> and we need to migrate this code to Oracle 8i)

> The query is stored in the sring known as S. The query returns results
> on the final command line:

>     Set SelectSQL = qdf.OpenRecordset(dbOpenSnapshot)

> This returns SelectSQL back to the original source as a recordset.
> Yet, it works perfectly on Oracle 7.3 database using ODBC Microsoft
> Driver 2.5 for Oracle, Oracle Driver 7.3 and 8.0 driver.

> There are problems with other drivers. Microsoft ODBC 2.5 driver for
> Oracle seems to work best but in checking out the recordcount -
> SelectSQL.RecordCount returns a value of -1.

> So I'm asking - are querydefs incompatible with 8i? I made the
> recommendation of overhauling the code to ADO (a lot of code)

> Any suggestions how we can 'fix' this as a crutch in order to make
> this a smooth but temp solution to 8i? Thanks! See code below:

> Function SelectSQL(s As String) As Recordset
> '--------------------------------------------------------------------
> '   Purpose:     Runs as SELECT SQL query.
> '
> '   Returns:     A recordset containing the results of the query.
> '--------------------------------------------------------------------
>     On Error GoTo SelectSqlError
>     Dim qdf As QueryDef
>     Dim i As Long

>     Set qdf = con.CreateQueryDef("")
>     qdf.SQL = s
>     qdf.MaxRecords = 0

>     i = con.CreateQueryDef("", "SELECT COUNT(*) " _
>         & Right$(s, Len(s) - (InStr(1, UCase$(s), "FROM") - 1))) _
>         .OpenRecordset(dbOpenSnapshot).Fields(0)

>     If i < 1 Then i = 1
>     qdf.CacheSize = i

>     Set SelectSQL = qdf.OpenRecordset(dbOpenSnapshot)

>     Exit Function
> '--------------------------------------

> SelectSqlError:
>       MsgBox "Function SelectSQL is Unable to run query: " &
> LCase$(s), _
>         vbCritical, Err.Number & " - " & Err.Description
> End Function