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.


Field Names --> New Field Names
ProblemDesc --> ProblemDescription
Reject --> TagNumber
DSCNinspect --> QREConfirmation
Watch Question

Top Expert 2016
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

just include the rest of the fields to the make table query
Most Valuable Expert 2014

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

Make table is nice and all -- but it won't have the relationships and referential integrity settings of the old table.
Top Expert 2016

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"

ggodwinQuality Engineer


I considered making a new table. This  is fine.