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