Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Location
    Nashville, Tennessee, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ALTER TABLE to RENAME COLUMN (Access XP SP2)

    I am trying to use the ALTER TABLE command to rename a field in my table and the pertinent code is:
    <font color=blue>Dim strSQL1 As String
    Dim db As DAO.Database
    Set db = CurrentDb()
    strSQL1 = "ALTER TABLE tblRawData RENAME F1 TO CenterNo;"
    db.Execute (strSQL1)
    Set db = Nothing</font color=blue>

    I get a Syntax Error in Alter Table statement message but I can't figure out what's wrong with my syntax. Can anyone clue me in?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ALTER TABLE to RENAME COLUMN (Access XP SP2)

    According to the online help, ALTER TABLE in Jet SQL does not support renaming columns. Neither can you change the Name property of a DAO field object once it has been appended to the Fields collection of a TableDef.
    I don't think there is a way to change a field name programmatically (except by creating a field with the new name, copying all data from the old field to the new one, and finally deleting the old field)

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: ALTER TABLE to RENAME COLUMN (Access XP SP2)

    As noted, you cannot use Jet SQL (directly or via DAO) to change a field name once field is appended to a table. To change field name programatically, you'd have to use ADOX Data Definition Language (DDL). Example:

    Public Sub RenameFieldADOX(ByRef strTblName As String, _
    ByRef strFldName As String, _
    ByRef strNewFldName As String)
    On Error GoTo Err_Handler

    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim strMsg As String

    cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTblName)

    tbl.Columns(strFldName).Name = strNewFldName

    ' Test Msg:
    MsgBox "Field " & strFldName & " in Table " & strTblName & _
    " renamed to " & strNewFldName & ''.", vbInformation, _
    "FIELD RENAMED"

    Exit_Sub:
    Set cat = Nothing
    Set tbl = Nothing
    Exit Sub

    Err_Handler:
    Select Case Err.Number
    Case 3265 ' Item not found in collection
    strMsg = "The specified field or table name was not found."
    Beep
    MsgBox strMsg, vbExclamation, "INVALID FIELD OR TABLE NAME"
    Resume Exit_Sub
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "RENAME FIELD ERROR MESSAGE"
    Resume Exit_Sub
    End Select


    End Sub

    Example of use with Northwind.mdb:

    RenameFieldADOX "Orders","EmployeeID","WorkerID"

    This successfully renamed the EmployeeID field in Orders table to WorkerID. To use this code, you need to set a reference to the ADOX library aka "Microsoft ADO Ext. 2.X for DDL and Security".

    HTH

  4. The Following User Says Thank You to MarkD For This Useful Post:

    chgeiselmann (2015-01-07)

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ALTER TABLE to RENAME COLUMN (Access XP SP2)

    Thanks, Mark, great code! I didn't know this was possible.

  6. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: ALTER TABLE to RENAME COLUMN (Access XP SP2)

    Actually, I didn't know if it was possible either, till I tried it just now. I knew you can't do it in DAO so decided to try ADO & see what happens....

  7. #6
    New Lounger
    Join Date
    Dec 2001
    Location
    Nashville, Tennessee, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ALTER TABLE to RENAME COLUMN (Access XP SP2)

    Thank you to both Hans and Mark. I'm going to give Mark's solution a try.

Posting Permissions

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