By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Whats wrong with this code

P: n/a
Hello!

Can someone tell me whats wrong with this piece of code:

Option Compare Database
Option Explicit

Sub retrieve()
Dim rst As ADODB.Recordset
Dim i As Integer

Set rst = New ADODB.Recordset

rst.Source = "qryVintager"
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open

i = rst.RecordCount

MsgBox "Record count: " & i
End Sub

'qryVintager' is predefined query. I run it directly in Access. According to
the book I use, this should work.

Thanks in advance for your help!
Jochen
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
JL
You have to explicitly move to the end of the recordset in order for Access
to know how many records it actually contains. In DAO the syntax is
something like rst.MoveLast...
"asdf" <as**@asdf.com> wrote in message
news:3f*********************@read.news.ch.uu.net.. .
Hello!

Can someone tell me whats wrong with this piece of code:

Option Compare Database
Option Explicit

Sub retrieve()
Dim rst As ADODB.Recordset
Dim i As Integer

Set rst = New ADODB.Recordset

rst.Source = "qryVintager"
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open

i = rst.RecordCount

MsgBox "Record count: " & i
End Sub

'qryVintager' is predefined query. I run it directly in Access. According to the book I use, this should work.

Thanks in advance for your help!
Jochen



-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #2

P: n/a
"asdf" <as**@asdf.com> wrote in message news:<3f*********************@read.news.ch.uu.net> ...
Hello!

Can someone tell me whats wrong with this piece of code:

Option Compare Database
Option Explicit

Sub retrieve()
Dim rst As ADODB.Recordset
Dim i As Integer

Set rst = New ADODB.Recordset

rst.Source = "qryVintager"
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open

i = rst.RecordCount

MsgBox "Record count: " & i
End Sub

'qryVintager' is predefined query. I run it directly in Access. According to
the book I use, this should work.

Thanks in advance for your help!
Jochen


Probably D Bugger will give you a clue, perhaps with a message;
invoking his/her name one may locate the exact line where the problem
(if there is a problem) occurs. If I had to guess, I'd guess the
number of records is greater than the largest number that will fit
into two bytes (integer).
Nov 12 '05 #3

P: n/a
"asdf" <as**@asdf.com> wrote in message news:<3f*********************@read.news.ch.uu.net> ...
Hello!

Can someone tell me whats wrong with this piece of code:

Option Compare Database
Option Explicit

Sub retrieve()
Dim rst As ADODB.Recordset
Dim i As Integer

Set rst = New ADODB.Recordset

rst.Source = "qryVintager"
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open

i = rst.RecordCount

MsgBox "Record count: " & i
End Sub

'qryVintager' is predefined query. I run it directly in Access. According to
the book I use, this should work.

Thanks in advance for your help!
Jochen


Probably D Bugger will give you a clue, perhaps with a message;
invoking his/her name one may locate the exact line where the problem
(if there is a problem) occurs. If I had to guess, I'd guess the
number of records is greater than the largest number that will fit
into two bytes (integer).
Nov 12 '05 #4

P: n/a
Hi JL,

That's not entirely true of ADO (it is of DAO).

If you are using a client side cursor location you don't need perform the
MoveLast method.

Adding rs.CursorLocation = adUseClient allows you to grab the count (and
generally gives you a richer enviroment by buffering the recordset
locally).

Also using rs.CursorType = adOpenKeyset will allow you to grab the count
with move last.

As is generally the case, the more features the larger the overhead, though
I've never seen any comparison stats myself (never looked).

Peter

"JL" <JL@JL.COM> wrote in news:3f**********@corp.newsgroups.com:
You have to explicitly move to the end of the recordset in order for
Access to know how many records it actually contains. In DAO the
syntax is something like rst.MoveLast...

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.