Member Avatar for Fenerbahce

Hi,
I use VB6 with Access. I have 4 records in my DBF file. When i do Recordcount, it says that i had -1 record. Why not 4? Help please
Thanks

Dim Conn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim Query As String

Private Sub Form_Load()

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Projects\VB 6\Listbox\Database.mdb;Persist Security Info=False"
Conn.Open

Query = "SELECT * FROM Table1"
Set Rs = Conn.Execute(Query)

List1.AddItem Rs.RecordCount
End Sub

  • 3 Contributors
  • 2 Replies
  • 135 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

The behavior is expected: ADO can return -1 for RecordCount when the Recordset doesn't know the total number of rows in its current cursor mode. The code shown in the first post opens the recordset in a mode (server-side / forward-only by default) that commonly doesn't support reporting the total. Note also the post text mentions DBF but the connection string points to an Access .mdb — that mismatch can cause confusion about providers and their cursor behavior.

Two quick reminders from the replies: is correct that using a client-side/static cursor will allow RecordCount to work, and is correct that forcing a full fetch (for example, moving to the last record) will also populate the count. Both have trade-offs: client-side cursors use more memory and moving to the last row can be slow or unsupported for forward-only cursors.

If all you need is the number of rows, the most reliable and efficient approach is to let the database engine count them:

Dim cn As ADODB.Connection
Dim rsCnt As ADODB.Recordset
Dim sql As String

sql = "SELECT COUNT(*) AS CNT FROM Table1"
Set rsCnt = cn.Execute(sql)
MsgBox rsCnt.Fields("CNT").Value
rsCnt.Close
Set rsCnt = Nothing

If you need the opened Recordset for other work, use this logic: check BOF/EOF first to detect an empty set; if RecordCount returns -1 then either reopen with a client/static cursor or run the COUNT(*) fallback. For large tables prefer COUNT(*); for small interactive datasets a client-side/static cursor is often acceptable.

Recommended Answers

All 2 Replies

Member Avatar for royaloba

Hi,
I use VB6 with Access. I have 4 records in my DBF file. When i do Recordcount, it says that i had -1 record. Why not 4? Help please
Thanks

Dim Conn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim Query As String

Private Sub Form_Load()

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Projects\VB 6\Listbox\Database.mdb;Persist Security Info=False"
Conn.Open

Query = "SELECT * FROM Table1"
Set Rs = Conn.Execute(Query)

List1.AddItem Rs.RecordCount
End Sub

try this
dim rs as new adodb.recordset

rs.CursorLocation = adUseClient
rs.open "Select * from table",conn
msgbox rs.recordcount
set rs=nothing

Member Avatar for debasisdas

try using rs.movelast
and then use rs.RecordCount

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.