Table Column Order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.

Thanks.

Misha
 
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.
Click to expand...

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.
 
Back
Top