Append query + VBA not appending in order i chose!

[gangel]

Hi ppl,



There is a reason for this. its not "100% needed" but even if not i want to learn what is going wrong!

SO i have a list of items with ITEMID and Item NAME

I was looking to copy that table, reorder it in alphabetical order and then re-number it.

Therefore

1. Banana
2. Apple
3 Mango

would get reworked to be

1. Apple
2. Banana
3. Mango

(as an Aside, as this occurs i have a new table being created to link the old and new ItemID)


Once again... i know this sounds weird (eg WHY ARE YOU CHANGING THEIR PRIMARY KEY OF ID) but there is a reason... and its a long story :P


SO.

What I have is an APPEND query which is sorted in ascending order of ITEMNAME

When i look at this query, it looks correct. All 2000 items are in order

in VBA i do this:

'Export Items
DoCmd.OpenQuery "QuItemCreator"
CurrentDb.Execute "ALTER TABLE TmpLinkItems ALTER COLUMN [LogikItemID] NUMBER"
CurrentDb.Execute "ALTER TABLE TmpLinkItems ADD [ItemID] AUTOINCREMENT PRIMARY KEY NOT NULL"


DoCmd.OpenQuery "QuUpdateItemLink"

Therefore it changes the current auto primary key to just NUMBER and then adds a new ITEMID Key in auto increment.

THIS WORKS.

But for some reason it then loses its ordering and starts from items in the AS section of the alphabet... so it now is no longer correctely ordered... or more commonly, the first 200 items are put further down the table!??

I hve NO idea why this is occuring but can anyone help me out here?

On another nte, every time i run the code it runs DIFFERENTLY and different items are in order!!!

[RuralGuy]

Have you specified the order of the data in a query?