Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2001
    Location
    London
    Posts
    3

    Question Unanswered: Truncate Access97 tables

    Is there a way of truncating an Access97 table short of

    1) DELETE FROM tbl
    2) Recreating the table structure and then dropping trhe original (using DAO)

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    dim strSQL as string

    ' here you remove all the records from the table, leaving it empty.
    strSQL="Delete * From tblTable;"
    docmd.execute strSQL

    ' here you can delete the table
    strSQL="Drop Table tblTable;"
    docmd.Execute strSQL

    HTH

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Aseervatham ,

    I've often also desired a TRUNCATE statement in Access.

    But, alas, there is none.

    From what I understand, the TRUNCATE TABLE statement, in MS-SQL, is no different from the DELETE FROM statement, except that updates to the log segment do not occur.

    Since Access doesn't maintain a transaction log, (except when specifically asked to do so), there would not normally be performance benefit to having a TRUNCATE TABLE statement in Access.

    I have, however, used the TRUNCATE TABLE statement in a number of different languages (with different syntax) and found, from a programming standpoint, it was a handy thing to have around.

    Ken

  4. #4
    Join Date
    Nov 2001
    Location
    London
    Posts
    3
    Thanks,

    I guess my real issue is that Access seems to take longer to delete the contents of a large table than it takes to make a structure only copy (in code, or manually) and then swap names and delete the original

    ...Bearing in mind that any reduction in .mdb file size will not be apparent until compaction

Posting Permissions

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