Moving Columns in tables

[RamziSaab]

I want to use a code to add a field in certain tables (and i know how to do this with SQL)...however i would like the column that is added to be and the begining of the table and not the end...is there a way to do this...(i know there is no difference where the column is but in the long run it will make things simpler!!

 

[RobCPA]

There are a couple of ways to add a field to a table - through SQL (Alter Table) and through DAO tabledefs and fields collections. Through SQL, the field is added to the end of the table. You can determine the place of the field using VBA (DAO).

When a field is added to a table, it has an Ordinal Position property which defines the order of the field within the table. It is possible to add a field at the beginning of the table, but you would need to change the OrdinalPosition property of the original fields to create room for the field that you are adding, and the OrdinalPosition value would have to be set at a value less than all of the other fields. All this is possible through VBA (DAO). For more information, take a look at the Access VBA Help files for the OrdinalPosition property including the associated code example.

Rob