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

VBA search not in order!!

P: 4
I have an Access database representing a set of Cities and contains an abstract set of coordinates. I have a procedure which after some calculation produces a chart in HTML showing the distances between these cities. Problem is that the procedure dose not list the Cities in correct order. For some reason the list starts from record 10. It is important to keep the same order as I store these distances in an array to be used in other procedures... please help in figuring out what is going on. Is it Access issue...
Dec 29 '11 #1

✓ answered by Stewart Ross

This is a characteristic of all SQL implementations - there is no concept of 'order' as such in the table itself, so no reliance at all can be placed on the order in which you have stored the records. If you think about it, the concept of 'order' is one which does not affect the storage of the underlying data at all - it is a presentational device, and one capable of multiple interpretations (for example, ordering your cities by name, or by distance from an arbitrary point, for instance, in ascending or descending order - none of which changes how the data is stored).

Relational databases are based on Set Arithmetic. Sets are not positionally-dependent; for example, the sets {1, 2, 3}, {3, 2, 1}, {3, 1, 2}, {1, 3, 2}, {2, 3, 1} and {2, 1, 3} are all equivalent.

As advised in the excerpt you posted above, you will need to use a query ordered on one of the underlying fields. If you do not have such a field, you can add a numeric field to the table specifically as a 'sort order' field, then set the value in sequence to reflect the 'correct' order of your cities. In your query you can then sort the city list on this arbitrary sort field instead of whatever it is sorted on at present.

-Stewart

Share this Question
Share on Google+
4 Replies


P: 4
After googling, if found this related answer...is this true, there is no other way only to convert my table to query??!!

"A Table has no order. When you open a Table type recordset, Access will return the records in whatever order it finds convenient - this might be in Primary Key order, or disk storage order, or some other order; it's altogether arbitrary!

If you want the records returned in a specific order, use a Query with an OrderBy clause sorting the records as desired (this should be very quick given that there is a Primary Key index on the three fields), and use a Dynaset rather than a Table type recordset."
Dec 29 '11 #2

P: 4
Thanks all, I got it. Yes the answer is to convert the database into Query!! This will ensure the records are in order when performing search in VBA.

best regards,
Dec 29 '11 #3

Expert Mod 2.5K+
P: 2,545
This is a characteristic of all SQL implementations - there is no concept of 'order' as such in the table itself, so no reliance at all can be placed on the order in which you have stored the records. If you think about it, the concept of 'order' is one which does not affect the storage of the underlying data at all - it is a presentational device, and one capable of multiple interpretations (for example, ordering your cities by name, or by distance from an arbitrary point, for instance, in ascending or descending order - none of which changes how the data is stored).

Relational databases are based on Set Arithmetic. Sets are not positionally-dependent; for example, the sets {1, 2, 3}, {3, 2, 1}, {3, 1, 2}, {1, 3, 2}, {2, 3, 1} and {2, 1, 3} are all equivalent.

As advised in the excerpt you posted above, you will need to use a query ordered on one of the underlying fields. If you do not have such a field, you can add a numeric field to the table specifically as a 'sort order' field, then set the value in sequence to reflect the 'correct' order of your cities. In your query you can then sort the city list on this arbitrary sort field instead of whatever it is sorted on at present.

-Stewart
Dec 29 '11 #4

P: 4
Stewart,
Thanks for making time to replay to my query.
Dec 29 '11 #5

Post your reply

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