Problem with query results when called from code

JP-C

Registered User.
Local time
Today, 10:51
Joined
Jan 29, 2007
Messages
14
Hey all, i've got an odd problem with a query i'm trying to call from code.

The query itself is nothing exotic, just a regular selection query with a few filters on it.

When i run the query from the gui i get the correct results, 11 records, but when i call it from code it only returns 1 record. It returns the first record from the correct resultset but doesn't return the other 10.

I have no idea why it's doing this, i've used code like this plenty of times and never had problems before. :confused:

I tried it like this:

Public Function test()

Dim db As Database
Dim rs_rsz As DAO.Recordset
Dim i As Integer

Set db = CurrentDb()
Set rs_rsz = db.OpenRecordset("myquery")

rs_rsz.MoveFirst

For i = 0 To rs_rsz.RecordCount - 1
<dostuff>
Next

End Function

And like this:

Public Function test()

Dim db As Database
Dim rs_rsz As DAO.Recordset
Dim qdf As QueryDef
Dim i As Integer

Set db = CurrentDb()
Set qdf = db.QueryDefs("myquery")
Set rs_rsz = qdf.OpenRecordset()

rs_rsz.MoveFirst

For i = 0 To rs_rsz.RecordCount - 1
<dostuff>
Next

End Function
 
Access does not load all the records up when you open

rs_rsz.MoveLast
rs_rsz.MoveFirst

For i = 0 To rs_rsz.RecordCount - 1
 
Great, that did it!

Do you have any idea why it doesn't load the results? I've used the same code with queries returning 200+ records. Is this just some Access quirk?
 
Just a quirk, probably to do with the way queris are optimized in the background.
another option which may be quicker with a big record set is

Do While not rs_rsz.EOF

......

Loop


HTH

Peter
 

Users who are viewing this thread

Back
Top Bottom