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?
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.
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.
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.
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)
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.