Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting all records (Access 2002)

    I'm almost embarrassed to post this one - it should be so simple. It worked in Access 97. (I think)
    I'm attempting to delete all the records in a table through code and can't get it to work. I've tried the following two methods with no luck:

    1.
    Set db = CurrentDb
    strSQL = "Delete tblMaterial* from tblmaterial;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL

    2.
    DoCmd.SetWarnings False
    db.Execute "Delete * from tblMaterial"

    Neither of these approaches worked.
    Please help - I'm pretty much brain-dead right about now.
    Thanks

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

    Re: Deleting all records (Access 2002)

    The first one won't work because there is no period/dot between tblMaterial and *. Either "DELETE tblMaterial.* FROM tblMaterial" or "DELETE * FROM tblMaterial" as SQL string should work. The DoCmd.SetWarnings False only affects the DoCmd.RunSQL one; temporarily disable it by inserting an apostrophe ' before it. Then run the code, and confirm. Do you get an error message, and if so, what does it say? Perhaps relational integrity prevents you from deleting the records - there might be related records in other tables.

  3. #3
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting all records (Access 2002)

    The missing "." was a typo when I entered the code into the posting. I think your last thought might be the issue - the related records. I'm enforcing referential integrity and not cascading deletes. Therefore, if I were to delete the records in tblMaterial table, I would be creating orphans. I'll look into this.
    Thanks for your quick response. As usual, you guys are great.
    Thanks,
    Lance

Posting Permissions

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