• Change Field name in Table with VBA

    Author
    Topic
    #466193

    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

    Viewing 3 reply threads
    Author
    Replies
    • #1206963

      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(“DatabasePathDatabase.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.

    • #1206983

      Awesome Andrew!  I am going to use the loop through the fields approach. 

      THANKS!

      Ken

    • #1207058

      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.

    • #1207212

      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 

    Viewing 3 reply threads
    Reply To: Change Field name in Table with VBA

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: