ALTER Statement to Change Column Names

UBSDev

New Member
Joined
Apr 25, 2006
Messages
26
Hello All,

I'm trying the following SQL Statement, but it doesn't seem to work in Access:

ALTER TABLE Table1 RENAME COLUMN [Field1] to [Field2];

Access doesn't seem to like the 'RENAME' statement....is there a better way to do this in Access?

r
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I don't think Access supports the RENAME statement, I could be wrong though.

You can do this with code:
Code:
Sub rename()
CurrentDb.TableDefs("table1").Fields("Field1").Name = "Field2"
End Sub

hth,
Giacomo
 
Upvote 0
Here's what I've discovered, hope it's helpful.

The DDL Alter Table statement provides you with the ability to ADD/DROP table fields and to change data types as well as modify or create indexes.

But you can not change the Field/Column Name.

You must Drop the Column, then Alter the Table and Add the column.

a) ALTER TABLE Employees Drop COLUMN column_name;
b) ALTER TABLE Employees ADD COLUMN column_name type(size);
--------------
Here is an example of the alter table statement which will add a column 'Emp_Email' to our Employees table:

ALTER TABLE Employees ADD COLUMN Emp_Email TEXT(25);

Weeks later we discover that 25 characters is not enough to hold all email address so we need to increase the length to 50 characters using the following alter table statement:

ALTER TABLE Employees ALTER COLUMN Emp_Email TEXT(50);

Below is the syntax for the alter table statement borrowed from Microsoft Access 2003 help system.

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
ALTER COLUMN field type[(size)] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }
 
Upvote 0
jackd

Wouldn't dropping a column/field drop the data as well?
 
Upvote 0
Norie,
Yes dropping the column would remove the data.

So, depending on what is really required and whether or not the column is already populated will influence what should be done.

MsAccess doesn't seem to support
Alter Table RENAME ...

If you have several tables, queries, modules etc, you will probably need to do impact analysis to see what uses the Column in question.

You can add a new column (with a different name) then copy the data from the old column to the new column, then drop the old column. However, if other queries, modules etc use the original column name, these would have to be changed to reflect the new name.
 
Upvote 0
jackd

That's what I thought.:)

I searched on ALTER TABLE but the only other SQL alternative I could find instead of RENAME was CHANGE.

And it doesn't look like that's supported by Access either.

In fact I'm not even sure RENAME/CHANGE are supported in 'proper' SQL.

I seem to recall they are additions that are available with the likes of MySQL.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top