[AccessD] Getting a Record Count using ADO

Michael Maddison michael at ddisolutions.com.au
Wed Nov 14 20:55:00 CST 2007


Havn't used ADO for a while but I have 2 comments.
I thought ADO didn't require movelast movefirst to get a rowcount?
Rather then populating 2 recordsets (potentially expensive) why not just
execute 2 select count(1) from table queries?
Then populate the recordset if required.

cheers

Michael M

Hi Susan
Brilliant - Many thanks
I chose dbOpenStatic

rs1.Open selSQL1, conn1
becomes
rs1.Open selSQL1, conn1, dbOpenStatic
Have a great day
 
Darren
-----------------
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Thursday, 15 November 2007 12:12 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Getting a Record Count using ADO

ADO's default cursor type is Forward-Only. You can't move backward, so
the MoveLast method returns an error.

ADO cursors:

dbOpenForwardOnly (the default): static copy, moves forward only
dbOpenStatic    static copy, all movement enabled
dbOpenDynamic    Updates are immediate and all types of movement are
enabled 
(not supported by Jet OLEDB provider), so Jet defaults to adOpenKeyset
dbOpenKeyset    All records are updateable, but you can't see additions
and 
deletions, all movements are enabled.

Choose your poison wisely. :)

Susan H.


Subject: [AccessD] Getting a Record Count using ADO


> Hi All
>
>
>
> When I use DAO Recordsets I can simply do a rs.movelast and 
> rs.MoveFirst to populate the rs and then can do an accurate 
> rs.recordCount - Simple
>
> When using ADO - IF I use the line rs.movelast - I get a strange error

> about backward fetching
>
> "Run Time Error -2147217884(80040e24)
>
> Rowset does not support fetching backward
>
> If I rem the line rs.MoveLast - the code works
>
> Code Snip Below (The real code does some updates etc on a second 
> recordset - snip not included for that)
>
> Anyone know about this - Is there a work around - I just want to 
> compare one rs.recordCount with another rs2.RecordCount and if they 
> differ then do something
> - else forget about it
>
> Make sense?
>
> Many thanks in advance
>
> DD
>
>
>
>
>
> '#############This is connecting a an SQL Server dB
>
> Dim conn1 As New ADODB.Connection
>
> Dim rs1 As New ADODB.Recordset
>
> Dim selSQL1 As String
>
> Dim intRecordCount As Integer
>
> Dim i As Integer
>
>
>
> selSQL1 = "SELECT * from BillRun"
>
> conn1 = f_SetSQLSVRConnection '<--SQL Server connections set up and 
> passed from a function here
>
> conn1.Open
>
>
>
> rs1.Open selSQL1, conn1, dbOpenStatic
>
>
>
> intRecordCount = 0
>
>
>
> With rs1
>
>    If Not rs1.BOF And Not rs1.EOF Then
>
>        'rs1.MoveLast <---- If I leave this line in - I get the error
>
>        rs1.MoveFirst
>
>
>
>        Do Until rs1.EOF
>
>          End If
>
>             End With
>
>            rs1.MoveNext
>
>            intRecordCount = intRecordCount + 1
>
>        Loop
>
>    Else
>
>    End If
>
> End With
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list