Fields in Access 2007 table moving around, out of order

Well, this thread was easy to find. I was worried that I was going nuts.

I see the same issue in Access 2007. I'm developing a c++ shell that sits on top of an Access database to encode, organize, and present zillions of bits of information associated with a complex manufacturing task. My database has 56 tables at present. When I added the 56th table, I started seeing the columns re-arranging themselves in my more complex tables as they were opened.

I developed a c++ Access database interface class years ago. I just linked into that existing code behind the new interfaces. Queries that pull complete records with all fields based on a unique primary key are based on the order of fields in the variantdata structures being the same as the order in DesignView. That is, yes, I assume the order is the same as in DesignView. I'd never seen anything to the contrary.

The first time I saw the re-ordered columns I freaked and immediately opened DesignView only to see that nothing there had changed. I went back to the open table and the order had changed more. On a hunch, I closed that, went back to DesignView, inserted a blank field after the primary key -- the first field -- deleted the blank field, and saved. When I closed DesignView and opened the table it had reverted to the same field order as presented in DesignView.

I only have a small portion of my data in place, just enough for testing and development, but that's still a lot of data, a lot of time and testing. I'm unwilling to test my shell on top of the database when I can see the fields re-sequenced. I don't want to risk doing anything that's irreversible and since I know how to force the fields to re-sequence per the DesignView order, I do that whenever I see them jumble.

So the question is .... will the order in which database pulls based on the classes and methods of afxdb.h via the SQL query "SELECT * FROM " are loaded into the variantdata structures used to transfer fields to the calling code change as well, or will the sequence orders always match DesignView?

Might be the wrong place to ask... but I figure I have to start somewhere.

One of the possible reason is about bit boundary. If you have fields (length in bits) as  8, 1, 8, 1 (bits), that order would take 32 bit (since the third field has to start on a 8 bit boundary)  in comparison to storing 8, 8, 1, 1  which takes only 24 bit ( the fourth field boundary being bit, it can be written in the same octet that the third field). It may also be about required fields versus nullable fields, or other reason linked to storage space or speed optimization.

This sounds reasonable, although it still seems odd to me that this kind of optimization would be forced on the output, especially since I don't recall this happening in earlier versions of Access.

I checked again with the table where I experienced this, and interestingly enough, if I import the Excel worksheet without any added Primary Key fields, it imports in the "correct" field order.  But if I import it with an Autonumber Primary Key field, the fields shift around.

Original order, field types: A (Text), B (Date/Time), C (Date/Time), D (Text), E (Text), F (Text), G (Text), etc.

New order with Autonumber, field types: New (Autonumber), A (Text), B (Date/Time),E (Text), C (Date/Time), D (Text), F (Text), G (Text), etc.

However, the other two shifts that occurred were Text type fields situated between two Text type fields, moving in between two other Text type fields.  In all three cases, the shift was the same: the fields moved two places up (Design View) or to the left (Datasheet View).

Dave

I suspect this is the right answer for my second example.  I didn't realize this could happen (drag a field into a different location without changing the Design View order), but I tested it myself and, sure enough, that's what happened.  Thanks, Scott.