Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Huntsville Alabama

    Smile Unanswered: Deleting All data from MS Access database

    I have a simple request.

    I want to build a query that removes all data from all tables.

    This query does a nice job of listing all user tables in a MS Access db.

    SELECT [MSysObjects].[Name] FROM MSysObjects
    WHERE [MSysObjects].[Type]=1 And Left([MSysObjects.Name],4)<>'MSys'

    Now I want to wrap that with a DELETE to purge all the data from each table.

    Can some one finish this for me? Or show me a better way?


  2. #2
    Join Date
    Dec 2003

    Try This

    Dim db As database: Set db = CurrentDb
    Dim rs As Recordset: Set rs = db.OpenRecordset("SELECT [Name] FROM MSysObjects WHERE [Type]=1 And [Name] NOT like 'MSys*'")

    While Not rs.EOF
    db.Execute ("DELETE * FROM " & rs.Fields(0))

    Set rs = Nothing
    Set db = Nothing

    Make sure you have the DAO object library referenced or this won't work.

    It seems to typically be under c:\program files\common files\microsoft shared\dao\

Posting Permissions

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