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.
Sandra SmithRetiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
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.
0
Sandra SmithRetiredAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
try this code

Dim db As DAO.Database, td As DAO.TableDef, fld As DAO.Field
Set db = CurrentDb
For Each fld In db.TableDefs("NameOFTable").Fields
    If fld.Name = "Field1" Then
        fld.Name = "Field2"
    End If
Next
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pcelbaCommented:
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.
0
Sandra SmithRetiredAuthor Commented:
This does exactly what I want, thank you.  Yes, it is imported, but once imported, the name then has to be changed.
0
pcelbaCommented:
What? Rey's solution works? Unbelievable!
0
Sandra SmithRetiredAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.