473,698 Members | 2,574 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 2267
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
24279
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
10002
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
2306
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
2454
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
15707
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
1585
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
8009
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
4746
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
9931
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
3672
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
8683
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
8610
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,...
0
9031
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8902
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,...
0
7740
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6528
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
4372
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3052
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
2339
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.