ADD COLUMN in a specified position?

[Zoon]

Can I add a column to a jet 3.5 database in a specified column position using SQL?

I tried the following and they did not work; ALTER TABLE [Products] ADD COLUMN [New Things] TEXT(20) BEFORE [Entered Date], ALTER TABLE [Products] ADD COLUMN [New Things] TEXT(20) AFTER [Description].

This works but it puts New Things in as the last column; ALTER TABLE [Products] ADD COLUMN [New Things] TEXT(20).

 

[Queryman]

Can you add a null value column in the desired position when you create the initial table that you are trying to alter and then update it with the value you want using an UPDATE statement instead of using the ALTER statement?

QueryMan

 

[stoggers]

Hi,

What about defining a view with the columns in the appropriate order?

Stoggers.

 

[Zoon]

Thanks for the help. Query Man is a clever name, it sounds like a comic book hero. I am new to forums and SQL but I will look into both suggestions. Zoon

 

[rac2]

An added thought. The order of columns in the table is not meaningful in SQL. When order is important the columns can be listed explicitly in the SELECT clause.

There is another forum for Jet SQL; different RDBMS have different ways of re-ordering columns (apparently column order is important to users if not to SQL). For example, with MS SQL Server Enterprise Manager in Table Design a new column can be inserted between existing columns. When you do that EM creates a new table with the columns in the new order and deletes the old table. That is how you would do it using SQL statements.

CREATE TABLE definining all the columns in the order you like.

INSERT new table SELECT colz, cola, colb, FROM old table.

DROP old table.