Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    87

    Unanswered: Can You Rename Column Names all at Once?

    Hello,

    Odd question. I have a Table that has about 140 columns. I also have an excel spreadsheet that has a two lists, one of which are the current Table Column names and one is the list which i want them to be.

    Is it possible to build an update query to change the Column names all at once?

    Example:
    OLDNAME1 = NEWNAME1
    OLDNAME2 = NEWNAME2
    OLDNAME3 = NEWNAME3
    and so on.

    What do you think?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could do it by a query (I think) you could also do it by directly editing the column names in the table editor

    but at 140 columns Id be suspicious that you have a non normalised design, and if so, that is going to be a problem over time
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It can be done in VBA:
    Code:
    Sub RenameColumns(ByVal TableName As String, ParamArray NewNames() As Variant)
    
        Dim dbs As dao.Database
        Dim tdf As dao.TableDef
        Dim i As Long
        
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs(TableName)
        For i = 0 To tdf.Fields.Count - 1
            tdf.Fields(i).Name = NewNames(i)
        Next i
        tdf.Fields.Refresh
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Sub
    Usage:
    Code:
    RenameColumns "MyTable", "field1", "field2", "field3",  "field4", "field5", "field6"
    With MyTable being the name of the table and Field1, Field2, ... being the new names of the columns.
    Have a nice day!

  4. #4
    Join Date
    Aug 2010
    Posts
    87
    Thanks all!

    The non normalized data (if it is) is necessary because this is being exported as a CSV and imported, as one file. The data is not being pulled in from anywhere, is static, non reliant on any other table and there is no duplicate data! I'm not sure if that's the definition of normalized or not but that's what it is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •