Saud
Posts: 15
|
| Posted: 07/13/2004, 5:00 AM |
|
I am trying to open a database using custom code and getting the recordcount of a certain query. The problem is that I am getting "-1" as a result for the recordcount.
I tried to use other types of opening the database, but I am getting more errors.
The code I am using now that returns the "-1" value is "
Dim Connection, SQL, RecordSet
Dim NoOfRecords
Set Connection = New clsDBConnSMS
Connection.Open
SQL = "SELECT * FROM Users WHERE User_Subscription_Ends >#" & Date() & "# ORDER BY User_ID ASC"
Set RecordSet = Connection.Execute(SQL)
NoOfRecords = RecordSet.RecordCount
NoOfRecords is always set to "-1"
Any help, tips, or comments are welcomed.
Thanks in advance.
Saud
|
 |
 |
marcwolf
Posts: 361
|
| Posted: 07/13/2004, 6:46 AM |
|
Hi
In many ADO solutions the Recordcount property is not reported correctly
Far easier is to use a count(*)
Faster if you have a unique key is to use the
count(rec_id)
Remeber - the more fields you return in a query - the slower it will be. Never use the ' * ' when you only need a couple of fields.
Take Care
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
|
|
|
DonB
|
| Posted: 07/13/2004, 7:23 PM |
|
For any "good" SQL implementation, COUNT(*) will be fastest. By "good" I
mean one that maintains system tables with the number of rows stored
therein. A COUNT(*) should result in one fetch from the metadata, not even
touching the actual data table. This has the further benefit of not
interfering, nor being interfered with, by any "writer" thread that may
otherwise block a direct COUNT (one that involves reading from the data
table).
Now, as to which are "good" SQL implementations. Hmmm, Oracle, SQL Server
are on the list. Probably mySQL, too. Access, "maybe", I wonder though
since it's not multi-threaded - probably there would be no benefit since all
readers/writers are handled sequentially anyway (or so I've read)..
For reference, Joe Celko provides a very good treatment of the intricacies
of SQL in his book "SQL For Smarties".
--
DonB
logging at http://www.gotodon.com/ccbth, and blogging at
http://ccbth.gotodon.net
"marcwolf" <marcwolf@forum.codecharge> wrote in message
news:640f3e7a94e3ff@news.codecharge.com...
> Hi
>
> In many ADO solutions the Recordcount property is not reported correctly
>
> Far easier is to use a count(*)
>
> Faster if you have a unique key is to use the
>
> count(rec_id)
>
> Remeber - the more fields you return in a query - the slower it will be.
Never
> use the ' * ' when you only need a couple of fields.
>
> Take Care
>
> Dave
>
>
> _________________
> ' Coding Coding Coding
> Keep Those Keyboards Coding.
> Raw Code!!!!!!!
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
