Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Emptying a table (2000)

    What's the easiest way to empty a table of data from within a macro? Please don't say 'delete it and copy the structure back to it' as the table(s) in question have a relationship and therefore cannot be deleted without first removing the relationship. I want to do this so that I can intoroduce new data into the table without simply appending to the end of the existing data (some of which may not now be relevant). Thanks in advance.

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

    Re: Emptying a table (2000)

    Why do you want to do this from a macro?

    The delete statment is :
    DELETE * FROM tablename

    I would set this up in a query, and if you really have to use a macro, then just use the OpenQuery command.

    Pat

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

    Re: Emptying a table (2000)

    There are several possibilities:

    1. You can create a delete query that deletes all records from the table, and open this query from your macro with the OpenQuery Action.
    2. You can use the RunSQL action with an SQL instruction that looks like DELETE * FROM tblSomething

    Note: I tend to avoid macros and use VBA code instead (in this case DoCmd.OpenQuery or DoCmd.RunSQL). Code is much easier to debug and to maintain than macros, and also more powerful.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Emptying a table (2000)

    If this table has related records in another table, which one is the "one" side of the relationship and is referential integrity enforced? If RI is enforced and this is the "one" table, then you won't able to delete the records without deleting the "many" side records first. If this is the "one" table and RI is NOT enforced, you're going to wind up with orphan records in the "many" side table.
    Charlotte

Posting Permissions

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