asked on

Can Alter Table statement be used to change column names in an ACCESS 2010 table

I have a process that repeatedly imports tables, but I need to change fields names on several columns in several tables, can alter table be used or what would be the easiest? I want to create a procedure in VBA that will do this automatically so I do not have to do this manually every time.

Pavel Celba

Yes and no... You may use ALTER TABLE to DROP certain columns or ADD new columns. It will be easy and fast when the table is empty. If you need to preserve some data in renamed column then you have to add new column first then copy all data from the old column (UPDATE statement) and drop the old column.

More info: https://msdn.microsoft.com/en-us/library/bb177883(v=office.12).aspx

If you are importing data to an empty table then you could think about the whole table deletion/creation before each import.

Sandra Smith

ASKER

These are primary key columns that the prior developer created using autonumber so they cannot be dropped and moved to a new column, I need to change the name of the existing column, if this cannot be done, then I understand but want to confirm.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

No, to change the column name this way is really impossible (unless you know the Access database structure and write the column name directly by some low level function which I don't recommend).

BUT you are talking about import... The standard import should be done to a temporary table where column names do not matter and successive processing which also checks data validity can populate rows in the real indexed table.

Sandra Smith

ASKER

This does exactly what I want, thank you. Yes, it is imported, but once imported, the name then has to be changed.

Pavel Celba

What? Rey's solution works? Unbelievable!

Sandra Smith

ASKER

Believe it! Actually, what is going on is I am updating a messy database and the tables are from the production version, which is quite different from the new one so I need a way to import all the tables from the production into the new version, but clean them up automatically rather than having to do all this manually. This process is done as they input new data and I need to have a current version of information to text against their reporting structure. I am posting various questions related to this problem, but, yes, Ray's solution does exactly what I need.