Specifying Column Position When Using ALTER TABLE

[Janie]

I cannot recall how to specify the position of a column that is added when
using an ALTER TABLE statement. Can someone help me out?

Thanks

 

[Douglas J. Steele]

It's not possible, but then the order of fields in a table shouldn't matter
to anyone.

If you do have a requirement for a specific order, create a query that has
the fields in the correct order, and use the query wherever you would
otherwise have used the table.

 

[Janie]

yes, actually in this case the order of the columns DOES matter because
1) there are a variable number of columns with varying names which change
with every use of the form
2) therefore a query specifying column names is out of the questin
3) because when the process finishes is uses a ws.copyfromRecordset to pass
the data to Excel (customer requirement)

so ... please do not presume location does MATTER

The columns seem to be placed randomly (I know ther are not but what is the
pattern?) I had expected them to appear in the order they were added --
sometimes this is the case -- many times it is not.

So what gives? And I seem to recall something about COL(i) where i is an
integer specifying the ordinal position of the column

Anybody else????

 

[Douglas J. Steele]

Relational Database Theory maintains that the order of fields does not
matter, just as the order of rows doesn't matter. Therefore, to the best of
my knowledge, DDL does not allow for ordering the columns.

Having the number of columns varying with each use of the form implies that
your database isn't designed correctly. Even if it is, though, why would
that make a query "out of the questi[o]n"? You can dynamically change the
SQL of a query just as easily as you can run DDL to alter the table, and you
can use a query as the source of a recordset just as easily as you can use a
table.

Perhaps if you described the problem you're trying to address, someone will
be able to suggest a better approach.