Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Find Database & Delete Object (2007)

    I am running a report from a temp table created by a make table query.

    I want to write an if…then…else statement to ensure that the temp table is deleted before I run the make table query. How would I do this?

    If ??????? then
    DoCmd.RunSQL "DROP TABLE temptable"
    else

    end if
    Easy Access

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

    Re: Find Database & Delete Object (2007)

    The simplest way is to suppress error messages while trying to delete the table:

    On Error Resume Next
    DoCmd.RunSQL ...
    On Error GoTo 0 ' or an error handler

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Database & Delete Object (2007)

    In addition to Hans' response, I do it this way
    <pre>Dim strTempTable1 As String
    strTempTable1 = "YourTableName"

    'check temp table existence
    If DCount("*", "MsysObjects", "[Name]='" & strTempTable1 & "'") = 1 Then
    'table exists - so delete
    DoCmd.DeleteObject acTable, strTempTable1
    Else
    'table does not exist, do nothing
    End If

    'call temp table create Sub
    Call CreateTempTable(strTempTable1)
    </pre>

    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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