Recrodcount on linked tables

  • Thread starter Thread starter Jad
  • Start date Start date
J

Jad

Hi there

I've been having problems showing my count before and
after inserting records into an ODBC (SQL server
database) table from my Access 2003 application,
rs.recordcount always shows 1 so the difference between
the before and after counts comes up as zero. It doesn't
matter if I use rs.movelast after the update. I found
that the local table shows the correct count using the
same code so I'm equessing this is due to the link. Is
there a way to efficiently get a record count of a table
in this situation?

thanks
Jad
 
Check DAO / ADO on RecordCounts. A number of different types of
Recordsets returns 1 as RecordCount regardless of the number of actual
Records.
 
Hi Jad,

It sounds like you are opening an ADO
recordset using the Execute method of a
Command object (if I have misunderstood,
please post your code).

When you you use the Execute method to
open a recordset, you *always* get a read-only,
forward-only recordset.

The CacheSize of a forward-only recordset
defaults to 1. One main purpose of all the cursor
types and locations in ADO is to reduce drain on
resources. In this case, ADO says, "Why keep
more than one record in memory at a time?"

Actually, I would think in this case you would get a
record count of -1, not 1?

To get an accurate record count, in ADO you need
to open the recordset with the Open method with a
cursor type of adOpenStatic (a client-side cursor
will always be adOpenStatic).

Dim rs As ADODB.Recordset
Dim strSQL As String

Set rs = New ADODB.Recordset
strSQL = "SELECT ..."
With rs
.Source = strSQL
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
'use Open method, not Execute
.Open Options := adCmdText
End With

MsgBox "Record Count = " & rs.RecordCount

rs.Close

Set rs = Nothing

or, more simply, try

rs.Open "Select ....", CurrentProject.Connection

or, pass a Long variable in a Command's Execute
method of your insert SQL

Dim lngRecAff As Long
Dim cmd = ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection

strSQL = "INSERT INTO ..."

cmd.CommandText = strSQL
cmd.CommandType = adCmdText

cmd.Execute RecordsAffected:=lngRecAff

MsgBox "Records Inserted = " & lngRecAff

or...forget ADO altogether

MsgBox "Record Count = " & DCount("*","Select ...")

Please respond back if I have misunderstood
(with your code).

Good luck,

Gary Walter
 
the past post
or, more simply, try

rs.Open "Select ....", CurrentProject.Connection
Click to expand...

should read:

or, more simply, try the following if the connection
uses a client-side cursor

rs.Open "SELECT ...", CurrentProject.Connection

or, if not sure

rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText

or

rs.CursorLocation = adUseClient
rs.Open strSQL, CurrentProject.Connection,,adLockReadOnly, adCmdText

Enough choices? 8-)

Good luck,

Gary Walter
 
Hi Gary

Thanks for the tip, adOpenStatic works perfectly.

Really appreciate your help.

cheers
Jad
-----Original Message-----
the past post


should read:

or, more simply, try the following if the connection
uses a client-side cursor

rs.Open "SELECT ...", CurrentProject.Connection

or, if not sure

rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText

or

rs.CursorLocation = adUseClient
rs.Open strSQL,
Click to expand...
CurrentProject.Connection,,adLockReadOnly, adCmdText
 
Back
Top