473,748 Members | 2,294 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

rs.RecordCount linked to a sql server db

6 New Member
I have some code in an ONCURRENT event that is running a query and then counting the number of records returned. However the RecordCount is always returning 1. I know the query works .. i put a msgbox and capture the query its running and when i run it in a query it returns the all the records.

The interface is in access but it is linked to a SQLServer database...

Dim db As Database
Dim rs, rs2 As Recordset
Dim strSql As String
Dim orgCount, nomCount, estimate As Integer

'Initialize to return Null on error.
Set db = CurrentDb

strSql = "SELECT qryCeremonyProg ram.organizatio nName FROM qryCeremonyProg ram where qryCeremonyProg ram.ceremonyId = " & Parent.ceremony id & " GROUP BY qryCeremonyProg ram.organizatio nName"

Set rs = db.OpenRecordse t(strSql)

orgCount = rs.RecordCount 'Return the number of distinct records.
MsgBox (orgCount)
Nov 22 '07 #1
2 2272
smook
6 New Member
Oh ... well I solved it myself. I guess you have to move through the recordset first so adding:

rs.moveLast

before the rs.RecordCount seemed to clear that up.
Nov 22 '07 #2
ADezii
8,834 Recognized Expert Expert
Oh ... well I solved it myself. I guess you have to move through the recordset first so adding:

rs.moveLast

before the rs.RecordCount seemed to clear that up.
You hit the nail on the head, some Recordsets you must explicitly traverse in order to get an accurate RecordCount, some Recordsets return the number of Records actually accessed (1), which seems to be your case. Whenever I need an accurate RecordCount, I always include the following line in my code:
Expand|Select|Wrap|Line Numbers
  1. rs.MoveLast:rs.MoveFirst
Nov 22 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
24284
by: David Gray | last post by:
Hello all, Having problems connecting to an Oracle 9i database from within SQL/Server 2000 using the Security/Linked Servers feature. Server1 (SQL/Server) ----------- Windows Server 2003, Standard edition MS SQL/Server 2000 Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools
4
10005
by: pheonix1t | last post by:
hello, I'm trying to create a linked server from an SQL2000 to a Unify ELS (very old, odbc is version 1) database on SCO unix. The odbc driver is old but it works fine when used by applications for creating reports. What I want to do is make a linked server from the SCO box to the win2k box. So, I'm using enterprise manager to create the linked server. The DSN of the unify database is CORE_OFFICE1 I'm getting 'Error 7399: OLE DB...
1
2309
by: TBlair | last post by:
I am about to start working on a project where I would be required to dynamically create linked servers during the execution of an application. One of the requirements is for the Link Servers to be created and dropped before and after the retrieval of the data. My question is about any type of cap on the number of linked servers SQL Server 2000 can have registered at any single time. If I find out that there is some type of cap, I would...
4
2457
by: arichie | last post by:
Can anyone point me where can I find a good architectural document on how the Linked server works in SQL Server. I am not asking about how to use Linked server, I am interested in know how it is defined and architected. Our project needs a data transfer from SQL server database to the Oracle database, I am trying to find out whether can we use DTS or create a linked server and execute a sql query to copy data. Thanks in Advance. Allen
8
15709
by: Crazy Cat | last post by:
Hi, When I click on the properties of a linked server, all the General properties are read - only, which means that if I want to edit any general properties I have to delete the linked server and recreate it. Needless to say this is a pain. Is this by design or some kind of bug? Is there a workaround? Thanks
1
1589
by: tim.pascoe | last post by:
I've been trying to work with Linked Servers for the first time, with mixed success. My problem right now is in trying to write a View using a linked server. The linked server is another SQL-Server, and it was set up using the 'Enterprise Manager' interface. When the server was created, the name was given as the full server address - name.pyr.ec.gc.ca. When I try and use this name in a new view, I enter the name in the query as...
2
8013
by: Larry Bertolini | last post by:
Is there a way to read data from a linked server, within a transaction, without using DTC? The data on the linked server is static, therefore there is no need for two-phase commit. There is no need for locking data on the linked server, because it is not being updated (either from the remote server, or from the local server). I don't want to run DTC because:
6
4747
by: Neil | last post by:
After creating a linked server to a remote server, I needed to log in using sp_addlinkedsrvlogin to get my stored procedure to work. However, I noticed that after stopping SQL Server and the DTC and then restarting both, that my stored procedure worked without having to execute sp_addlinkedsrvlogin. Is the log-in information stored in the machine, such that if SQL Server is stopped or the server is rebooted, on does not have to execute...
5
9934
by: Neil | last post by:
I am getting time-out errors when I try to perform a simple delete on a linked server. The command is: Delete From MyTable Where PKID=12345 I have tried executing this command directly from Query Analyzer, but it just times out. However, when I run it from QA against the server itself (rather than from my local server against a linked server), it executes immediately. Similarly, if I run the same SQL command through an ODBC linked...
1
3674
by: BillCo | last post by:
There seems to be a lot of confusion around the groups about linking to an Access mdb with the SQL Server Jet OLE DB provider and I havent been able to find a straight forward solution. Basically, I have an Access MDB (A2K) on one server and a SQL Server DB (2005 std ed.) on another - Both on the same network. I'm trying to create a linked server object in the SQL server to view data in the mdb. I've set it up and it works - but only from...
0
8983
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8822
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
9310
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6072
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3298
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2774
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2206
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.