SELECT * query returning as if SELECT TOP 2

[gudisdum]

I am calling a SELECT * using this code

Dim db As Database
Dim rs As Recordset
Dim s As String
Dim x As Integer
Set db = CurrentDb
s = "SELECT * FROM [

http://WWW];"

Set rs = db.OpenRecordset(s, dbOpenDynaset)
x = rs.RecordCount
MsgBox (x)

There are over 200 entries in the table yet it only returns the top one. I have tried to set it up in the query builder, where it works fine, and call the query instead of my string but the same thing happens. Can someone please tell me what I am doing wrong.

Thanks much
Doug

 

[BoxHead]

Set rs = db.OpenRecordset(s, dbOpenDynaset)
[b]rs.MoveLast
rs.MoveFirst[/b]
x = rs.RecordCount
MsgBox (x)

Access needs a bit of a wake up call before it actually reads all of the records.

If you are just trying to get the number opf records in the table '

http://WWW',

you could also use
=DCount("*","WWW")



HTh



John





When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[gudisdum]

Actually that was my debug code to try and find out what it was pulling from the table.
The movelast movefirst worked like a charm.
Thanks much
Doug

 

[JerryKlmns]

Doug,

Only ClientSide cursor location and a Keyset cursor type would get you the RecordCount property of a recordset the way you did it. For a ServerSide cursor location and Dynaset (but not ForwardOnly, which would return -1 for non empty recordset and 0 otherwise]) cursor type, you have to populate the whole recordset as John showed. But if you only need the RecordCount, prefer the aggregate DCount function that John says.

FYI
There is an open debate, where it is mentioned that this RecordCount property should not be trusted.

 

[stix4t2]

Doug

DCOUNT( "*", "

http://WWW")

will return the number of records in a table or query.

 

[jedraw]

Doug,

You can also try the following modification to your code

Dim db As Database
Dim rs As Recordset
Dim s As String
Dim x As Integer
Set db = CurrentDb
s = "SELECT Count(*) as DougCount FROM [

http://WWW];"

Set rs = db.OpenRecordset(s, dbOpenDynaset)
x = rs!DougCount
MsgBox (x)