Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Delete All Records in Table (Access 2002)

    What is the VBA code for deleting all records in a table?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete All Records in Table (Access 2002)

    I have used the following code successfuly but there may well be better ways.

    Dim strSQL As String
    Dim tmpDatabase As Database

    'Delete all temporary records
    strSQL = "DELETE * FROM Table_Name;"
    Set tmpDatabase = CurrentDb
    tmpDatabase.Execute strSQL, dbFailOnError

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete All Records in Table (Access 2002)

    When running the suggested code it hangs on the Dim tmp Database As Database line with the following error message:

    User Defined type not defined.

    Am I missing something here?

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

    Re: Delete All Records in Table (Access 2002)

    Try choosing the DAO 3.6 reference, then define the following in your code:

    Dim tmp Database As DAO.Database

    If you define recordsets you will have to do the same thing here as well.

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

    Re: Delete All Records in Table (Access 2002)

    Patt's suggestion will work. Access 2002 also lets you do this without a reference to the DAO library:

    CurrentDb.Execute "DELETE * FROM Table_Name"

    or

    DoCmd.RunSQL "DELETE * FROM Table_Name"

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

    Re: Delete All Records in Table (Access 2002)

    Both Hans suggestions will work, but if you use the DoCmd.RunSql version don't forget to surround this command by the DoCmd.SetWarnings commands to stop the dialog box from popping up.
    Eg.
    DoCmd.SetWarnings False
    DoCmd.RunSql ....
    DoCmd.SetWarnings True

  7. #7
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete All Records in Table (Access 2002)

    Hans suggestion works great, and thanks to Pat for the warning -- however I decided to leave the popup message in to advise the user that they are about to delete all the records.

Posting Permissions

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