Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Is it possible to read the names of the fields in an Access table and rename the fields? *I have three tables, one with several fields, and I need to change the name of the fields. *I would prefer to do it programmatically if possible. *




    Any ideas are greatly appreciated.




    Ken





  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    This is Possible using DAO or ADODB but DAO is easier.

    IF the Tables are in a linked database, then the Database will need to be referenced differently
    but the Basic Principle is as below.

    This only shows how to Target a single field
    If you need to do all fields then you will need to use a loop

    Code:
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("Customers")
    
    Set fld = tdf.Fields("City")
    fld.Name = "Town"
    
    dbs.Close
    Set dbs = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    
    MsgBox "Changed"
    If the Tables are in another Access Database then use

    set dbs=dbEngine(0).OpenDatabase("DatabasePath\Databas e.mdb")

    If you need to loop through all the fields in a Table then use a For Each Loop

    Code:
    For Each fld In tdf
          fld.Name="NewName"
    Next
    In this latter case you may well need to employ some sort of Conditiona IF Statement
    to determine what the changes are.

    Make sure NO ONE else is using the database and no tables are open in design.


    Andrew

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Awesome Andrew! *I am going to use the loop through the fields approach.*




    THANKS!




    Ken


  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    A note of caution here - renaming fields can cause all sorts of issues if you are using queries, forms or reports that use them. The AutoCorrect option will supposedly deal with them if you have it turned on, but there are some issues with it, and it won't fix references in VBA. You might want to consider one of the global rename utilities that are available.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Wendell,

    Thanks for the note. *I am just trying to "clean up" some inconsistent naming conventions in a some Access tables prior to upsizing the tables to SQL Server. *I will be creating queries, forms, reports and SQL views once moved to SQL Server.




    Thanks for continuing to serve us neophytes by sharing your knowledge.




    Ken*








Posting Permissions

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