We help IT Professionals succeed at work.

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
Comment
Watch Question

Top Expert 2016
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
Most Valuable Expert 2014

Commented:
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.
Top Expert 2016

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
ggodwinQuality Engineer

Author

Commented:
I considered making a new table. This  is fine.