Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2005
    Posts
    313

    How to rename the column name in MS Access table

    Hi:

    MS Access
    In query:
    I write

    ALTER TABLE table_name
    RENAME COLUMN old_name to new_name;

    But, it doesn't work, because "Rename" is NOT avaliable.
    How can I fix that?

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,275
    look under TableDef in the help has some good exmples
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Just like anything else in Microsoft Access, you can do almost any one thing about six different ways. It all depends upon which environment or environments you want to play in. Here is a little function using ADOX that will do the task at hand:

    NOTE:
    You will need to Reference the Microsoft ADO Ext for DDL and Security (ADOX) Library.

    Code:
    Public Function RenameColumn(ByVal StrgDB_Name As String, StrgTableName As String, _
    				StrgOldColumnName As String, StrgNewColumnName As String) As Boolean
       On Error GoTo Err_RenameColumn
     
       'Create a Catalog object
       Dim Cat As ADOX.Catalog
       Set Cat = New ADOX.Catalog
       Cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & StrgDB_Name
     
       'Create a table object
       Dim Tbl As ADOX.Table
       Set Tbl = New ADOX.Table
       Set Tbl = Cat.Tables(StrgTableName)
       Tbl.Columns(StrgOldColumnName).Name = StrgNewColumnName
     
       'Return that the function was Successfull.
       RenameColumn = True
     
    Exit_RenameColumn:
       'Clean up
       Set Cat = Nothing
       Set Tbl = Nothing
       Exit Function
     
    Err_RenameColumn:
    	MsgBox "There was an Error while Renaming the Table Column [" & StrgOldColumnName & "] to [" & _
    	StrgNewColumnName & "] within the Table named [" & StrgTableName & "] which is located in the " & _
    	StrgDB_Name & " Database.", vbCritical, "Column Rename Error"
    	GoTo Exit_RenameColumn
    End Function

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  4. #4
    Join Date
    Sep 2005
    Posts
    313
    Excuse me, Could you attach one example for me? If you don't want, it is fine. Forget about it.
    I do NOT quit understand.
    It's fine.
    Thanks a lot. Thanks.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,419
    Quote Originally Posted by accessman2
    Excuse me, Could you attach one example for me? If you don't want, it is fine. Forget about it.
    I do NOT quit understand.
    It's fine.
    Thanks a lot. Thanks.
    what precisely do you think Cyberynx has provided to you in code element of his posting?

  6. #6
    Join Date
    Sep 2005
    Posts
    313
    Hey, man. I already said "Forget about it". It is optional.
    Doesn't matter. Please read "Forget about it".

    Thanks.

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Hmmmm...It almost appears that perhaps there is a slight language barrier here.

    accessman2...if you can tell me what your native language may be, then perhaps I can translate the code for you. It may make it easier for you to understand. You see, I'm like the Pope. I can speak 15 different languages.

    Or..is it that you do not understand VBA code very well? If this is the case, then I simply will not waste my time anymore creating the suggestive code (since I do almost everything with code in my applications).

    Is it that I don't post enough comments in the code? If so (anyone) then please let me know.

    If you would still like to have a sample application of the above code, I would be more than happy to accomodate you. That is if you really do want it.

    sincerely,

    .
    Last edited by CyberLynx; 10-23-05 at 20:36.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  8. #8
    Join Date
    Sep 2013
    Posts
    1
    thank you very much for this excellent code

    however
    i required this additional information
    for a complete solution
    a) comment out the line "On Error GoTo Err_RenameColumn" because this obscurs the actual error message
    d) "To use this library, open the code window, choose References on the Tools menu, and check the box beside: Microsoft ADO Ext x.x for DDL and Security"
    which i found browsing to C:\Program Files\Common Files\System\ado\msadox28.tlb
    c) need a function like below to actually utilise the supplied code
    --------------------------------------------------------------
    Sub ren_cols()

    Dim blah As Boolean
    blah = RenameColumn("<path><filename>.mdb", "<tablename>", "<old_col_name>", "<new_col_name>")
    End Sub

Posting Permissions

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