ADO RecordCount -1

Hi - I'm stumped on using ADO to count the number of rows in a SQL
Server table. I'm using VB6. I've populated the connection fields
elsewhere, and can see that it is actually reading the table (by
checking the returned values while debugging), but I'm getting the
infamous "-1" as a return from the .recordcount method. I'm trying to
populate 'numrec' with the count of rows.

Thanks in advance for any help you can provide.

Below is the specific code snippet, there is more code before and after
of course, but this is the section I'm having trouble with:

dim numrecs as long

strConnect = "Provider=SQLOLEDB.1" _
& ";User ID=" & txtUID _
& ";Password=" & txtPWD _
& ";Initial Catalog=" & txtDatabase _
& ";Data Source=" & txtServer

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strConnect
End With

Set rst = New ADODB.Recordset
With rst
.Open Source:=txtTable, _
ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic

If Not .BOF And Not .EOF Then

.MoveLast

numrecs = .RecordCount

End If

.

Relevant Pages

  • VB IDE Collating Seq error
    ... Dim cnn As ADODB.Connection ... Dim rst As ADODB.Recordset ... Dim strSQL As String ...
    (microsoft.public.vb.database.ado)
  • Re: how to Trim Trailing spaces on SQL table
    ... Dim rst As New ADODB.Recordset ... Dim cnn As New ADODB.Connection ... Dim strSql As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Retrieve and update 1 record at a time
    ... Dim cnn As New ADODB.Connection ... Dim rst As New ADODB.Recordset ... The reason> for the flag is so when there are multiple users, a SaleID will only be> retrieved once. ...
    (microsoft.public.excel.programming)
  • Re: Get RecordCount
    ... I know that in order to get the record count instead> of a -1 I should set properly the rst Cursor Type. ... > Dim cnn As ADODB.Connection ... > Dim cmd As ADODB.Command ...
    (microsoft.public.access.modulesdaovba)
  • Re: rst! - whats that?
    ... > Dim cnn As ADODB.Connection ... > Dim rst As New ADODB.Recordset ... integers are integers, strings are strings ** ...
    (microsoft.public.access.modulesdaovba)