Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Remove a field by code (2000)

    I have a table (result of a make-table query). Before appending records from this to another table, I would like to remove the key field from the table. How can I remove, via code, a field from a table?
    Thanks

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

    Re: Remove a field by code (2000)

    The following code uses DAO, so you must set a reference to the Microsoft DAO 3.6 Object Library in the Visual Basic Editor (menu option Tools | References...)

    This procedure will remove an index from a table:

    Sub RemoveIndex(strTable As String, strIndex As String)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTable)
    tdf.Indexes.Delete strIndex
    Set tdf = Nothing
    Set dbs = Nothing
    End Sub

    and this procedure will remove a field from a table:

    Sub RemoveField(strTable As String, strField As String)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTable)
    tdf.Fields.Delete strField
    Set tdf = Nothing
    Set dbs = Nothing
    End Sub

    If you want to remove a field, you must first remove all indexes of which the field is part. (Note: if the field is involved in a relationship with other tables, you should delete the relationship too)

    Example of use:

    RemoveIndex "tblTest", "PrimaryKey"
    RemoveIndex "tblTest", "AnotherKey"
    RemoveField "tblTest", "KeyField"

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Remove a field by code (2000)

    Yes! Thank you.

Posting Permissions

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