rs.recordset not working

[MeSteve]

I am trying to use rs.recordset to get the number of records in my table

Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("my database path")
Set rs = db.OpenRecordset("tbl_Projects")

MsgBox rs.RecordCount

This returns 1 even though it will populate an array fully using do until
..EOF loop. What am I missing?

 

[Ken Warthen]

MeSteve,

Try this:

Dim db as Database
Dim rs as Recordset
Dim intRecordCount as Integer

Set db=CurrentDb
set rs=db.openrecordset("MyTable")
With rs
.MoveFirst
.MoveLast
intRecordCount = .RecordCount
End With
rs.close
set rs=nothing
MsgBox intRecordCount


Ken Warthen
(e-mail address removed)

 

[Truper]

Ken,

I am also having a problem with RecordCount, but my RecordCount is returning
-1. I am using VBA in Access 2003. Here is my code.

Dim db As Object
Dim conn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim iRecCount As Long

Set db = Application.CurrentData
Set conn = Application.CurrentProject.Connection
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT * FROM Ticket WHERE PeriodFK = 1"
Set rst = New ADODB.Recordset
With rst
.Open cmd, , adOpenDynamic, adLockOptimistic
.MoveFirst
.MoveLast
iRecCount = .RecordCount
Debug.Print iRecCount ' This prints -1 in the debug window.
.MoveFirst
Do While .BOF = False And .EOF = False
' This loop occurs 20 times because there were 20 records in the
recordset
Loop
.Close
End With

Any suggestions why the RecordCount property is not working?
--

\\\\\\______________//////
( ) ( )
J
\_____/

 

[JimBurke via AccessMonster.com]

I think that the recordset type must be static or keyset to get a valid
recordcount. This example is using a type of dynamic. As for the first
example, I don't know what happens when you use openRecordset - maybe
recordcount works with the default recordset type that that method uses.

Ken,

I am also having a problem with RecordCount, but my RecordCount is returning
-1. I am using VBA in Access 2003. Here is my code.

Dim db As Object
Dim conn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim iRecCount As Long

Set db = Application.CurrentData
Set conn = Application.CurrentProject.Connection
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT * FROM Ticket WHERE PeriodFK = 1"
Set rst = New ADODB.Recordset
With rst
.Open cmd, , adOpenDynamic, adLockOptimistic
.MoveFirst
.MoveLast
iRecCount = .RecordCount
Debug.Print iRecCount ' This prints -1 in the debug window.
.MoveFirst
Do While .BOF = False And .EOF = False
' This loop occurs 20 times because there were 20 records in the
recordset
Loop
.Close
End With

Any suggestions why the RecordCount property is not working?

MeSteve,

[quoted text clipped - 29 lines]

 

[Dirk Goldgar]

Ken,

I am also having a problem with RecordCount, but my RecordCount is
returning
-1. I am using VBA in Access 2003. Here is my code.

Dim db As Object
Dim conn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim iRecCount As Long

Set db = Application.CurrentData
Set conn = Application.CurrentProject.Connection
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT * FROM Ticket WHERE PeriodFK = 1"
Set rst = New ADODB.Recordset
With rst
.Open cmd, , adOpenDynamic, adLockOptimistic
.MoveFirst
.MoveLast
iRecCount = .RecordCount
Debug.Print iRecCount ' This prints -1 in the debug window.
.MoveFirst
Do While .BOF = False And .EOF = False
' This loop occurs 20 times because there were 20 records in the
recordset
Loop
.Close
End With

Any suggestions why the RecordCount property is not working?

I think you have to use a client-side cursor. Try this:

Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseClient
.Open cmd, , adOpenDynamic, adLockOptimistic

' ...