Changing Column Name in an existing table

[CD Tom]

I have a table that I would like to change the column names on a few columns depending on a name selected by the user.
I can delete the existing columns and then add the new ones using the Alter table command but was wondering if there is a way to just change the column name?
Also after the process is completed I want to change the column name back to the original, I know the location of the columns in the table so is there a way to use the column number to change the column name.
Thanks for any help.

CD

[Stewart Ross]

Hi Tom. Whilst it is certainly possible to use the ALTER TABLE statement to change the structure of a table, or alternatively to do so using the Append method of the Fields collection in DAO Recordset processing, I would advise against doing so.

There is no need to make physical changes to the table structure. Instead, just use a SELECT query and apply an alias to each of the field names you want to change using the AS statement, which you can do in VBA code relatively easily. The (simplified) example below shows how:

strSQL = "SELECT Field1, [Some Field] AS [" & strNewValue & "], Field3 FROM YourTable"

The SQL can be set dynamically as the recordsource of a form or report, or appended to a QueryDef, for use in whatever application you envisage.

I am not at all clear why you need to rename columns in response to user input, but as that is what you ask I do think that aliasing the physical names is a more flexible and less error-prone solution than changing the physical structure of a table. The other advantage is that there is no need to physically change the field names back after use.

-Stewart