473,324 Members | 2,370 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

RecordCount with a linked table as the record source

I am trying to get a correct recordcount from a recordset that
references a linked table as the source.

Here is the code:

Dim db1 As DAO.Database
Dim rs1 As DAO. Recordset
Set db1 = CurrentDB()
Set rs1 = db1.OpenRecordSet("tbl_CID", dbReadOnly)
Debug.print rs1.RecordCount

When this code is executed, the Immediate window shows the printed
value to be 1, where there is actually 136 records in the table.

Any ideas what might be the cause of this?

Regards,
Charles

Sep 11 '06 #1
2 5886
ChasW wrote:
I am trying to get a correct recordcount from a recordset that
references a linked table as the source.

Here is the code:

Dim db1 As DAO.Database
Dim rs1 As DAO. Recordset
Set db1 = CurrentDB()
Set rs1 = db1.OpenRecordSet("tbl_CID", dbReadOnly)
Debug.print rs1.RecordCount

When this code is executed, the Immediate window shows the printed
value to be 1, where there is actually 136 records in the table.

Any ideas what might be the cause of this?

Regards,
Charles
RecordCount is not accurate until you move to the last record. What would
be a lot more efficient though would be..

Dim db1 As DAO.Database
Dim rs1 As DAO. Recordset
Set db1 = CurrentDB()
Set rs1 = db1.OpenRecordSet("SELECT Count(*) as cnt FROM tbl_CID",
dbReadOnly)
Debug.print rs1!cnt

Its' almost always better to get the query engine to do as much work as
possible rather than VBA code.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Sep 11 '06 #2
>RecordCount is not accurate until you move to the last record. What would
be a lot more efficient though would be..

Dim db1 As DAO.Database
Dim rs1 As DAO. Recordset
Set db1 = CurrentDB()
Set rs1 = db1.OpenRecordSet("SELECT Count(*) as cnt FROM tbl_CID",
dbReadOnly)
Debug.print rs1!cnt

Its' almost always better to get the query engine to do as much work as
possible rather than VBA code.
Thanks a bunch.
Charles
Sep 11 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Mark Watkins | last post by:
I know for a fact that in my database, I have three records under the users table. However when I execute this code: --------------BEGIN CODE---------------- Set objConn =...
7
by: Serge Myrand | last post by:
Hi, I have an ADODB.RecordSet that RecordCount alway return -1 and this RecordSet is plenty of record! R.RecordSet = -1 R.eof = False R.BOF = False Is the cursor is lost somewhere?
20
by: Neil | last post by:
I have an Access 2000 MDB file with a SQL 7 back end. I have a main table with 50,000 records; and I have a selections table with 50,000 records for each machine that uses the database (about...
0
by: crypto_solid via AccessMonster.com | last post by:
I have been using a SQL database with a VB5 frontend for about 5 years. Works well. Unfortunately I don't have access to the source code. I was tasked with implementing a "job entry" application...
10
by: Robert | last post by:
How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that...
10
by: Jim Devenish | last post by:
I have a split front end/back end system. However I create a number of local tables to carry out certain operations. There is a tendency for the front end to bloat so I have set 'compact on...
0
by: GeryLuz | last post by:
Hi !! I have a problem showing a users table in an ASP page. It shows me only one record when i checked that there are more than one in my table. But when i print the RecordCount property of...
2
by: banderson | last post by:
Hello, I have a data entry form for a table with information about buildings and am having a problem making a combo box do what I want. I would like to make the combo box show a list of unique bldg...
1
by: tbeer | last post by:
Hello. I have created a 2007 database to help organize a golf outing event which requires the collection and tracking of a lot of linked data. Everything in my dbase is linked to my Main Business...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.