Strange Problem

[crowegreg]

I’ve created a DB for a customer. I’ve executed a particular form at least 100 times on my computer and it has always executed properly. On my customer’s computer, it will not execute properly all the time. I’ve been unable to determine what is causing the problem.

Here are the details:
Table Name: tbl Export IPG Orders
Fields: Group 1 – text
Group 2 – text
Order Number – long integer
Group Type – long integer

The Group Type is either 1, 2, or 3.

Here my statement:
DoCmd.RunSQL "INSERT INTO [tbl Export IPG Orders - 2nd] ( [Group 1], [Group 2], [Order Number], [Group Type] ) SELECT [tbl Export IPG Orders].[Group 1], [tbl Export IPG Orders].[Group 2], [tbl Export IPG Orders].[Order Number], [tbl Export IPG Orders].[Group Type] FROM [tbl Export IPG Orders] ORDER BY [tbl Export IPG Orders].[Order Number], [tbl Export IPG Orders].[Group Type]"

What this is supposed to do is insert all 4 fields into a table using the Order Number as the first ascending sort and the Group Type as the second ascending sort

Here’s an example of what the table should look like:
Group1, group2, 123456,1
Group1, group2, 123456,2
Group1, group2, 123456,3
Group1, group2, 123457,1
Group1, group2, 123457,2
Group1, group2, 123457,3

My problem is sometimes the sort does not work properly. Here’s an example
Group1, group2, 123544,2
Group1, group2, 123544,3
Group1, group2, 123545,1
Group1, group2, 123545,2
Group1, group2, 123545,3
Group1, group2, 123456,1
Group1, group2, 123456,2
Group1, group2, 123456,3


I can’t figure out what is happening. It’s not consistent. Thanks for your assistance!!

 

[Galaxiom]

Tables have no intrinsic order. The records will be inserted in an order that is entirely at the whim of Access.

Only by using an Order By in a query can you get them to appear in a designated sequence.

 

[crowegreg]

Isn't that what my insert statement suppose to being doing?

 

[Galaxiom]

Isn't that what my insert statement suppose to being doing?

You might suppose that is the case but you need to realise that tables are not spreadsheets. They don't have Rows but Records. Records do not have an order in a table.

It seems to be a concept that many new developers struggle with. No matter how you add records to a table they never ever adopt an order. It is absolutely impossible to control the sequnce of records in a table. Even when you order them with the header you are just being shown a built in query rather than the table. The order of records in the table is not changed.

Only by viewing the records subjected to an Order By clause can you have them display in a defined sequence. Even if you then take that ordered query and write the records to a new table they do not necessarily append in that order because there is no intrinic order in a table.

Unfortunately new developers are often led into the trap because records written to a new table will often appear in the order they have been added. But that is something that happens through luck. (Good or bad luck depending on how you look at it.)

You are going to have to redesign your application to assert order on the records using a query when you need to view or process them in a sequence.

If you have used the order of appearance in tables in any of your other applications you had better urgently rework them too because you have a "Dud App Working". ( I don't mean to be rude. It is just a play on "Dead Man Walking".)

 

[crowegreg]

I like your writing. Thanks. The most confusing thing about this, I've run this process on my computer over 100 times, and it's worked correctly everytime. I install on the customer's computer, and their is a problem the first time. For any future databases, I'll remember what you wrote that the table only holds the data, their is no order to it.

 

[Galaxiom]

IThe most confusing thing about this, I've run this process on my computer over 100 times, and it's worked correctly everytime.

You have been unlucky. When all you have seen is the records in the order they have been added you would have no reason to suspect anything other than that behaviour. It seems perfectly logical.

I was lucky. The first time I ever looked at a table it had dated records appearing all over the place so I was never under any illusion.

The reason it is done that way in databases (not just Access) is the way the table is accessed. Obviously it would not be practical to write the whole file every time a record is changed so the table is held in "pages" which can be changed without affecting the rest of the file. (Access has 25 records in a page if I remember correctly.)

When records are deleted they are just marked as deleted on the page. (I understand they are overwriten with the first record on the page too.)

When records are added, Access grabs a page and shoves the records wherever space is availalable rather than at the end. Compacting removes all the deleted records and fills the pages with valid records. It is actually not unlike defragging a file on a disk.

Anyway now you know and that is good thing.

Most of us are here are self-trained and it is surprising how long we carry myths that we picked up in our early experiences. Finding out the real story is one reason I am so active here. I have avoided so many problems before I encountered them throught the experience of others shared here.

 

[gemma-the-husky]

One way of FORCING the order to be the same as the insertion order, of course, is to add an autonumber field. This will definitely increment with each new record, and therefore will provide a way of reconstructing the original order.

 

[crowegreg]

Thanks. I'll keep that in mind.