Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete field in table (Access 2000)

    I want to delete all the fields in a table except the fields F1,F2 and F3.
    How can i do that?
    I can delete for example the field F7 like that:
    Set tdf = db.TableDefs("MyTable")
    Set fld = tdf.Fields("F7")
    tdf.Fields.Delete fld.Name

    However,is it possible to declare also the fields that should not be deleted?

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

    Re: Delete field in table (Access 2000)

    You can use a loop:

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim i As Integer

    Set db = ...
    Set tdf = db.TableDefs("MyTable")

    For i = tdf.Fields.Count - 1 To 0
    Select Case tdf.Fields(i).Name
    Case "F1", "F2", "F3"
    ' do nothing
    Case Else
    tdf.Fields.Delete tdf.Fields(i).Name
    End Select
    Next i

    Set tdf = Nothing
    ' If you opened an external database
    db.Close
    Set db = Nothing

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete field in table (Access 2000)

    Thanks Hans !

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete field in table (Access 2000)

    I somehow cannot manage delete the fields.Would you please see my attachment?

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

    Re: Delete field in table (Access 2000)

    I forgot one essential point: the first line of the For ... Next loop should be

    For i = tdf.Fields.Count - 1 To 0 [b]<big>Step -1</big>]/b]

    You'll get an error though, since the code will try to delete the field ID, but that is the primary key of the table. You can't delete a field that is part of an index or of a relationship. You must either leave the field ID in the table, or remove the primary key before deleting the field:

    tdf.Indexes.Delete "PrimaryKey"

    This line should be inserted between Set tdf = ... and For i = ...

Posting Permissions

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