Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Dec 2007
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    clear/delete a table (Access 2003)

    I am using the following to browse for a file to import its data into a table. This will create a table to put the data. I want to delete the previous table each time this is run. Is there something I can add to the following so there will be a clean slate each time?

    Private Sub Busboss_Click()
    Dim strFilter As String
    Dim strInputFileName As String
    strFilter = ahtAddFilterItem(strFilter, "Cruise data (*.XLS)", "*.XLS")

    strInputFileName = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, DialogTitle:="Please select an input file...")

    DoCmd.TransferSpreadsheet acImport, , "tblBusboss", strInputFileName, False, "A:Z"
    End Sub

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: clear/delete a table (Access 2003)

    You can either delete the table, or just delete all its records.

    To delete the table you need:

    DoCmd.DeleteObject acTable, "tblBusboss"

    To delete all records from tblBusboss you need this

    Dim sql as string
    sql = "Delete tblBusboss.* from tblBusboss"
    Currentdb.Execute sql

    Put either of these before the TransferSpreadsheet command.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: clear/delete a table (Access 2003)

    If there are any queries related to the tblBusboss it is better to empty it rather than delete it.
    Thanks
    chuck

Posting Permissions

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