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