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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,285
Office Version
  1. 365
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} }
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,919
Office Version
  1. 365
Platform
  1. Windows
jackd

Wouldn't dropping a column/field drop the data as well?
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,285
Office Version
  1. 365
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,919
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,139
Messages
5,546,184
Members
410,731
Latest member
keobongmacao
Top