Hello,

I am confused when a recordset is returned, why the recordcount is 
different for the following cases, although the sql statement retrieves 
the same records from the same table(table1 which has name field, that has 
four names). Can you please let me know? Thank You.

Santosh


Option Compare Database

'Case I
'RecordCount is 4, which is correct no.of records
'in Table1

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
  
sql = "SELECT FirstName FROM Table1;"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
MsgBox rs.RecordCount

'Case II
'Recordset.RecordCount is -1

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
 
 
sql = "SELECT FirstName FROM Table1;"
Set cn = CurrentProject.Connection
Set rs = cn.Execute(sql)
MsgBox rs.RecordCount

'Case III
'recordCount is 1 

dim db as dao.database
dim rs as rs.recordset
dim sql as string


sql = "SELECT FirstName FROM Table1;"
Set db= currentdb
set rs = db.OpenRecordset(sql)
msgbox rs.RecordCount

Check on the type of recordset that is being opened.  The first code you 
posted explicitly states adOpenKeyset as a parameter.  The other code you 
posted will use whatever defaults the system uses.  Try running code after 
you get the recordsets that goes to the last record and then check the 
recordcount.  You should check up on the different types of recordsets 
available as they differ quite broadly(some will not allow you to move 
backwards for instance).

Hope this helps you.

Phillip

> Hello,

> I am confused when a recordset is returned, why the recordcount is 
d> ifferent for the following cases, although the sql statement retrieves 
t> he same records from the same table(table1 which has name field, that 
has 
f> our names). Can you please let me know? Thank You.

> Santosh

> 
O> ption Compare Database

> 'Case I
'> RecordCount is 4, which is correct no.of records
'> in Table1

> Dim cn As ADODB.Connection
D> im rs As ADODB.Recordset
D> im sql As String
 >  
s> ql = "SELECT FirstName FROM Table1;"
S> et rs = New ADODB.Recordset
r> s.Open sql, cn, adOpenKeyset, adLockOptimistic
M> sgBox rs.RecordCount

> 'Case II
'> Recordset.RecordCount is -1

> Dim cn As ADODB.Connection
D> im rs As New ADODB.Recordset
D> im sql As String
 > 
 > 
s> ql = "SELECT FirstName FROM Table1;"
S> et cn = CurrentProject.Connection
S> et rs = cn.Execute(sql)
M> sgBox rs.RecordCount

> 'Case III
'> recordCount is 1 

> dim db as dao.database
d> im rs as rs.recordset
d> im sql as string

> 
s> ql = "SELECT FirstName FROM Table1;"
S> et db= currentdb
s> et rs = db.OpenRecordset(sql)
m> sgbox rs.RecordCount

--0-216344442-1017326604=:35693
Content-Type: text/plain; charset=us-ascii


 I also have found the RecordCount property of the Recordset object to be inacurate. I have been told and found it to work out fine
is to move to the last record then take a RecordCount (rs.MoveLast). 
Just a thought.
 
  Santosh Gurung <golbheda@y...> wrote: Hello,

I am confused when a recordset is returned, why the recordcount is 
different for the following cases, although the sql statement retrieves 
the same records from the same table(table1 which has name field, that has 
four names). Can you please let me know? Thank You.

Santosh


Option Compare Database

'Case I
'RecordCount is 4, which is correct no.of records
'in Table1

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

sql = "SELECT FirstName FROM Table1;"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
MsgBox rs.RecordCount

'Case II
'Recordset.RecordCount is -1

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String


sql = "SELECT FirstName FROM Table1;"
Set cn = CurrentProject.Connection
Set rs = cn.Execute(sql)
MsgBox rs.RecordCount

'Case III
'recordCount is 1 

dim db as dao.database
dim rs as rs.recordset
dim sql as string


sql = "SELECT FirstName FROM Table1;"
Set db= currentdb
set rs = db.OpenRecordset(sql)
msgbox rs.RecordCount

Lonnie Johnson 
ProDev, Builders of MS Access Databases
http://www.galaxymall.com/software/PRODEV 




---------------------------------
Do You Yahoo!?
Yahoo! Movies - coverage of the 74th Academy Awards®

To add to PRODEVs comment, I have always (in DAO) moved to last and then
first records (or vice versa)  before getting the record count - this
always gives me an accurate count.

Microsoft's literature does point out that there may be inaccuracies if you
do not do this


Typical code would be:

RS.MoveFirst
RS.MoveLast

RecordCount  = RS.Recordcount

Joe Dunn

*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************