Access SQL Update Query to change names of fields

I have a table and need to change the field names.

Table: DSCNSKPIUPDATE2

Field Names --> New Field Names
ProblemDesc --> ProblemDescription
Reject --> TagNumber
DSCNinspect --> QREConfirmation
ggodwinAsked:
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.

Rey Obrero (Capricorn1)Commented:
to do this using SQL, create a new table to carry the new field name, using a make table query

Select  ProblemDesc  As ProblemDescription, Reject As TagNumber, DSCNinspect As QREConfirmation
INTO DSCNSKPIUPDATE3
FROM DSCNSKPIUPDATE2

just include the rest of the fields to the make table query
0

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
Nick67Commented:
I am wondering about the gotcha in your question
Access SQL Update Query to change names of fields
Why not just go into the design view of the table and change the fieldnames?

It wouldn't be an update query that would change the name, it would be a DDL query.
For a one-off operation that seems like a fair bit of hassle
And, if you are changing fieldnames, turning on 'Perform Name AutoCorrect' in the database Options, and doing the update through the GUI, and then turning it off again will change all the other objects (queries, reports, and forms) at the same time.

It can get done through SQL.  I just can't see why you would want to do it that way , though

@Rey
Make table is nice and all -- but it won't have the relationships and referential integrity settings of the old table.
0
Rey Obrero (Capricorn1)Commented:
using VBA

Dim fld As DAO.Field, db As DAO.Database
Set db = CurrentDb
For Each fld In db.TableDefs("DSCNSKPIUPDATE2").Fields
    If fld.Name = "ProblemDesc" Then fld.Name = "ProblemDescription"
    If fld.Name = "Reject" Then fld.Name = "TagNumber"
    If fld.Name = "DSCNinspect" Then fld.Name = "QREConfirmation"
Next

db.close
0
ggodwinAuthor Commented:
I considered making a new table. This  is fine.
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.