[AccessD] Getting a Record Count using ADO

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