Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Delete Fields in tables (Access XP)

    I have substantially changed a database for a client and in doing so I have preceded the fields in tables with a 'z' where it's no longer required.
    Is there a way to use VBA to delete these fields from tables?

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

    Re: Delete Fields in tables (Access XP)

    The following code needs a reference to the Microsoft DAO 3.6 Object Library (in Tools | References in the Visual Basic Editor).

    Sub RemoveFields()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim i As Integer

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    For i = tdf.Fields.Count - 1 To 0 Step -1
    If Left(tdf.Fields(i).Name, 1) = "z" Then
    tdf.Fields.Delete tdf.Fields(i).Name
    End If
    Next i
    Next tdf

    ExitHandler:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Note: this code will fail if the field is part of an index. It could be extended to remove indexes involving fields starting with "z".
    Warning: make a backup copy of the database before running this code. Deleting fields cannot be undone.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Delete Fields in tables (Access XP)

    Thanks Hans
    To be safe I would also check for any tables starting with ~ or msys. Am I correct here?

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

    Re: Delete Fields in tables (Access XP)

    Good idea, although in my test database the MSys tables don't have fields whose name starts with "z". Still, better be safe than sorry.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Delete Fields in tables (Access XP)

    Thanks, I'll try that. After backing up of course.

Posting Permissions

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