Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to count a number of records in Ms Access using Visual Basic 6? 2
- Thread starter jpraman
- Start date
rbraunberger
Programmer
If your recordset is called rsABC, then do this:
I don't believe you need the MoveFirst and MoveLast commands if you're using ADO.
Hope that helps.
Code:
rsABC.MoveLast
rsABC.MoveFirst
RecordCount = rs.RecordCountI don't believe you need the MoveFirst and MoveLast commands if you're using ADO.
Hope that helps.
-
1
- #5
Question:
Doesn't the CURSORTYPE property effect the RecordCount property? If I open with CURSORTYPE=adOpenDynamic, RecordCount always seems to be -1. I have tried the MoveFirst/MoveLast and still RecordCount is -1.
However, opening adOpenStatic does return a RecordCount (I believe this is the client side cursor CCLint, correct?).
When opening for Dynamic access, is there any better way to get the number of records short of actually counting them all?
Thanks much,
msc
Doesn't the CURSORTYPE property effect the RecordCount property? If I open with CURSORTYPE=adOpenDynamic, RecordCount always seems to be -1. I have tried the MoveFirst/MoveLast and still RecordCount is -1.
However, opening adOpenStatic does return a RecordCount (I believe this is the client side cursor CCLint, correct?).
When opening for Dynamic access, is there any better way to get the number of records short of actually counting them all?
Thanks much,
msc
-
1
- #7
petermeachem
Programmer
select Count (*) AS TheCount From table
is normally quicker that a movelast
is normally quicker that a movelast
A client side cursor is always static.
A Static cursor can be done with a client or a server cursor.
The ForwardOnly and dynamic(use only if absolutely needed) cursors do not return a record count. So with these, just run a second query as petermeachem says to get the count.
You should use this anyways if using a Server side cursor (Static, KeySet, etc), which will be better that the movelast as petermeachem states, on larger amounts of data at least.
A Client cursor already should have an accuruate count available for you, as all of the records have been retreived, just as happens when you do a MoveLast with a server side cursor (KeySet or Static), UNLESS you are fetching records async. Then the cursor manager will automatically wait untill all records are retreived, defeating the purpose of running it async.
A Static cursor can be done with a client or a server cursor.
The ForwardOnly and dynamic(use only if absolutely needed) cursors do not return a record count. So with these, just run a second query as petermeachem says to get the count.
You should use this anyways if using a Server side cursor (Static, KeySet, etc), which will be better that the movelast as petermeachem states, on larger amounts of data at least.
A Client cursor already should have an accuruate count available for you, as all of the records have been retreived, just as happens when you do a MoveLast with a server side cursor (KeySet or Static), UNLESS you are fetching records async. Then the cursor manager will automatically wait untill all records are retreived, defeating the purpose of running it async.
![[ponder]](/data/assets/smilies/ponder.gif "[ponder] [ponder]")
)The reason why the record count = -1 is that we need to specify the cursor location. In case ADO is being used, you can specify the cursor location as
ConnectionObject.CursorLocation = CursorLocationEnum.adUseClient
By default, the cursor location is CursorLocationEnum.adUseServer
And once the command is executed, check for the state of the recordset before trying to get the record count.
If rsMyRecordSet.State = adStateOpen Then
Msgbox rsMyRecordSet.RecordCount
End If
ConnectionObject.CursorLocation = CursorLocationEnum.adUseClient
By default, the cursor location is CursorLocationEnum.adUseServer
And once the command is executed, check for the state of the recordset before trying to get the record count.
If rsMyRecordSet.State = adStateOpen Then
Msgbox rsMyRecordSet.RecordCount
End If
'==I used=='
msgbox "WE have total Records: " & Adodc1.Recordset.Recordcount
'==Another i used=='
Dim Counter As Long
If RS.BOF And RS.EOF Then
Else
RS.MoveFirst
Do While Not RS.EOF
Debug.Print Counter
Counter = Counter + 1
RS.MoveNext
Loop
End If
msgbox "WE have total Records: " & Adodc1.Recordset.Recordcount
'==Another i used=='
Dim Counter As Long
If RS.BOF And RS.EOF Then
Else
RS.MoveFirst
Do While Not RS.EOF
Debug.Print Counter
Counter = Counter + 1
RS.MoveNext
Loop
End If
petermeachem
Programmer
jonybd your second method is going to be incredibly slow
On my pc that counted 200,000 records in a minute, then I stopped it. With the debug out it did 500,000 in 30 seconds.
This
L = 0
Set rs = dbs.OpenRecordset("select count(*) As thecount from awdinput"
If Not (rs.BOF And rs.EOF) Then
L = rs("thecount"
End If
rs.Close
took 3 seconds
and
L = 0
Set rs = dbs.OpenRecordset("select * from awdinput"
If Not (rs.BOF And rs.EOF) Then
rs.MoveLast
L = rs.RecordCount
End If
took much the same, perhaps a little longer and using "select [registration no] from awdinput" was the same.
(did it in access as it was open at the time)
On my pc that counted 200,000 records in a minute, then I stopped it. With the debug out it did 500,000 in 30 seconds.
This
L = 0
Set rs = dbs.OpenRecordset("select count(*) As thecount from awdinput"
If Not (rs.BOF And rs.EOF) Then
L = rs("thecount"
End If
rs.Close
took 3 seconds
and
L = 0
Set rs = dbs.OpenRecordset("select * from awdinput"
If Not (rs.BOF And rs.EOF) Then
rs.MoveLast
L = rs.RecordCount
End If
took much the same, perhaps a little longer and using "select [registration no] from awdinput" was the same.
(did it in access as it was open at the time)
>The reason why the record count = -1 is that we need to specify the cursor location
Not quite true...
Specifying a Client side cursor will produce a static cursor. You can have a static, or keyset cursor with a server side cursor as well.
It has nothing to do with the cursor location, but instead with the type of cursor which gets created.
- Thread starter
- #16
A DBGrid/DataGrid is used to display data in a spreadsheet like format.
It gets it's Data from a DataControl (or recordset object).
A Datacontrol is a control which is placed on a form just like any other control, and wraps a recordset object.
The DataControl offers a simplier/compact method to open a connection to a data source, open a recordset to retreive data from that source, and allow a user to navigate through the retrieved records, as opposed to using a recordset object variable.
Once a data control successfully retrieves records from a data source, a bound control, like a DataGrid, can display these records.
A "DATA1" DataControl is used with DAO.
A "DBGrid" is a fully Bound Grid used with DAO. (Can be used in unbound mode as well).
In bound mode, this needs to be connected to a DAO "DATA" data control.
An "ADODC" DataControl is used with ADO OLEDB.
A "DataGrid" is a Bound Grid used with ADO. (CANNOT be used in unbound mode).
This can be connected to a ADODC or a recordset object variable.
It gets it's Data from a DataControl (or recordset object).
A Datacontrol is a control which is placed on a form just like any other control, and wraps a recordset object.
The DataControl offers a simplier/compact method to open a connection to a data source, open a recordset to retreive data from that source, and allow a user to navigate through the retrieved records, as opposed to using a recordset object variable.
Once a data control successfully retrieves records from a data source, a bound control, like a DataGrid, can display these records.
A "DATA1" DataControl is used with DAO.
A "DBGrid" is a fully Bound Grid used with DAO. (Can be used in unbound mode as well).
In bound mode, this needs to be connected to a DAO "DATA" data control.
An "ADODC" DataControl is used with ADO OLEDB.
A "DataGrid" is a Bound Grid used with ADO. (CANNOT be used in unbound mode).
This can be connected to a ADODC or a recordset object variable.
Hello,
I've got the following problem:
I connect to an access 2002 database through an ado control. The recordcount from the recordset = -1 although there are records (I get their values with the fields property). Now, I want to fill a true db grid with the records from the recordset, but because the recordcount = -1 the grid thinks that there are no records to display. I tried to set the IRowsetIdentity to true, but it doesn't work. Does anyone has a solution for this? Thanks...
I've got the following problem:
I connect to an access 2002 database through an ado control. The recordcount from the recordset = -1 although there are records (I get their values with the fields property). Now, I want to fill a true db grid with the records from the recordset, but because the recordcount = -1 the grid thinks that there are no records to display. I tried to set the IRowsetIdentity to true, but it doesn't work. Does anyone has a solution for this? Thanks...
Your problem probably is that you are using a forward only cursor.
You need to us a Static or Keyset cursor, which means not creating the recordset like this:
Set rs = conn.Execute("SELECT..."
But like this:
Dim rs As ADODB.Recordset
Set rs = NEW ADODB.Recordset
Set rs.ActiveConnection = Conn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.Source = "SELECT..."
rs.Open Options:=adCmdText
or
Dim rs As ADODB.Recordset
Set rs = NEW ADODB.Recordset
Set rs.ActiveConnection = Conn
rs.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
rs.Properties("IRowsetIdentity" = True
rs.Source = "SELECT..."
rs.Open Options:=adCmdText
Depending on the provider, you may not need to set the IRowsetIdentity (With the JET provider you will need to if using a server side cursor)
You need to us a Static or Keyset cursor, which means not creating the recordset like this:
Set rs = conn.Execute("SELECT..."
But like this:
Dim rs As ADODB.Recordset
Set rs = NEW ADODB.Recordset
Set rs.ActiveConnection = Conn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.Source = "SELECT..."
rs.Open Options:=adCmdText
or
Dim rs As ADODB.Recordset
Set rs = NEW ADODB.Recordset
Set rs.ActiveConnection = Conn
rs.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
rs.Properties("IRowsetIdentity"
= Truers.Source = "SELECT..."
rs.Open Options:=adCmdText
Depending on the provider, you may not need to set the IRowsetIdentity (With the JET provider you will need to if using a server side cursor)
- Status
Similar threads
- Locked
- Question
- Locked
- Question
