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

Read Order of Access Query Using VBA

P: 1
I am trying to read through and process an Access Query using VBA.

I have used the OpenRecordset method with parameters as below

OpenRecordset(sSourceRecordset, dbOpenDynaset)

Where “sSourceRecordset” contains the name of the query I wish to read.

I can then move through the records using .MoveNext

My problem is that the order in which this reads through the query is not either the sort order as currently applied to the query or the order determined by the primary key of the table on which the query is based.

If I display the query it is in the correct order if I remove the sort on the query it reverts to order I get, when reading as above, which seems to equate to the order in which items were added to the original table.

I have a unsatisfactory work around that involves using the query to generate a new table and then defining the primary key on that table to give the order required.
Sep 12 '07 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
I am trying to read through and process an Access Query using VBA.

I have used the OpenRecordset method with parameters as below

OpenRecordset(sSourceRecordset, dbOpenDynaset)

Where “sSourceRecordset” contains the name of the query I wish to read.

I can then move through the records using .MoveNext

My problem is that the order in which this reads through the query is not either the sort order as currently applied to the query or the order determined by the primary key of the table on which the query is based.

If I display the query it is in the correct order if I remove the sort on the query it reverts to order I get, when reading as above, which seems to equate to the order in which items were added to the original table.

I have a unsatisfactory work around that involves using the query to generate a new table and then defining the primary key on that table to give the order required.
Try this ...

OpenRecordset("SELECT * FROM " & sSourceRecordset & " ORDER BY FieldName", dbOpenDynaset)
Sep 12 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.