Misha said:
Does anyone know how to change the column order of an access
datababase (Access 2002) table using ADO. I am appending a column to
an existing table (ALTER TABLE....). The new column is added at the
end of the table. However, I would like to move the new column away
from the bottom but could not find any references of how to change
the order of the table columns using code.
If you can do it at all with ADO, I'm pretty sure you'd have to use ADOX
(Microsoft ADO Ext. 2.x for DDL and Security). You can do it readily
with DAO (Microsoft DAO 3.6 Object Library):
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim I As Integer
Set db = CurrentDb
Set tdf = db.TableDefs("Table1")
Set fld = tdf.Fields("SomeField")
fld.OrdinalPosition = 0
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
Note that setting the field's OrdinalPosition doesn't automatically
reset the corresponding OrdinalPositions of the other fields. Fields
with the same OrdinalPosition are ordered alphabetically.
