Oracle 7.3 and Oracle 8i with Visual Basic

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 Received on Wed Mar 20 2002 - 09:14:04 CST