CODEWITHSUNDEEP
ms-accessvba
msinfo
msinfo

Reputation: 1175

Ms Access 2003 VBA. Random order of table recordset?

I open a recordset (A) and use record value to process some data, and write a new recordset (B), with record value from recordset (A) and data value returned from processing. E.g 

RS A        RS B
123     789 grape
456     456 apple
789     123 three
112     123 two
        456 orange
        123 one
        112 blue
        112 green

Now what I see is that, instead of reading data from recordset (A) in sequential manner it reads data in random order. So when there is large volume of data and macro hangs up in between, I have to spend some amount of time, to check which records were written in recordset (B), and which were not.
Why it is so, instead of reading records in sequential manner, it reads in random order.
My code looks like below: 

Set objDb = Application.CurrentDb
Set RecA = objDb.OpenRecordset("select * from tblA") 
' am I opening recordset in proper manner
' or I have to fill other parameters too?
Set RecB = objDb.OpenRecordset("select * from tblB") 

While RecA.EOF <> True
RecA.MoveNext   ' move to next record
RecB.AddNew     ' add new 
RecB.Update     ' update
Wend

Regards.

Upvotes: 0

Views: 573

Answers (1)

Tom Collins
Tom Collins

Reputation: 4069

It's not random, it just looks that way. If you run it multiple times, you'll see that it comes out in the same order.

If you want to select the order it comes out in, then use the ORDER BY clause in your SQL. So, for you, it would be:

SELECT * FROM tblA ORDER BY {fieldname}

Upvotes: 1

Related Questions