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.
"Glenn" <dawriter67_at_hotmail.com> wrote in message
news:29c94448.0203200714.51727c34_at_posting.google.com...
> 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 Mon Mar 25 2002 - 20:57:23 CST
