There is a common misconception amongst Access users that the data is stored in the tables and is stored in the order in which the data has been input. That is a view that is very, very wrong. So, how are data stored, you may ask?

Well, in the absence of a primary key, data are stored in no particular order. The data will be stored within the file system wherever space can be found. Access will typically append the data to the end of the file, if the space is available, but it doesn’t necessarily do so.

So, the question may be, “what about data with a primary key?”. The answer to that question takes on a couple of parts. If there is a primary key, the data in the “table” will be attempted to be ordered by the primary key upon the Compact and Repair. However, it does not retain this ordering while records are added and deleted, until it attempts to order again when another Compact and Repair is performed.

Also, when you open a table to look at the data, you need to understand that you are not looking at the storehouse itself, but in fact you are really just looking at a query. This is quite like SQL Server in which looking at a table is really just a view.

The moral of the story is just that, if you want data returned in a specific order, you need to use a query and apply a sort to the data in the way that you want to see it.