Results 1 to 8 of 8
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Check for object before delete (A2K SR1)

    I am running several delete object commands such as
    DoCmd.DeleteObject acTable, "WBS" to drop tables before re-importing them. However, if the table doesn't exist an error message is received. How can you check for the table to see if it exists (or in this case does not exist) to know if the delete object statement should be run? I want to bypass the statement if the table does not currently exist or I create a dummy table to be deleted. Any ideas??
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Check for object before delete (A2K SR1)

    Well the simplest thing to do is attempt to delete it and trap the error. You could do that by inserting an On Error Resume Next just ahead of the delete object commands. If the Error = 0, the table existed and you deleted it, if it's anything else you probably don't care.
    Charlotte

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Check for object before delete (A2K SR1)

    Thanks for the advice. I will give it a try.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for object before delete (A2K SR1)

    Very minor point - if you're doing much else in your code, you'll want to put an on error goto whatever after your deletion code to re-enable the error checking. If you don't have explicit error checking (naughty!) use on error goto 0.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Check for object before delete (A2K SR1)

    Thanks,

    I was thinking about how to re-enable the error coding. I don't typically do much with the error codes but see how they can benefit if used.
    Regards,

    Gary
    (It's been a while!)

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for object before delete (A2K SR1)

    Just another suggestion ... I haven't used this, but it's title seems to address your question:
    Modules: Check if an object exists in the database

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Check for object before delete (A2K SR1)

    This does appear to be another solution. I will give it a try and let you know how it worked out. Thanks for the link.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Check for object before delete (A2K SR1)

    I could not get the link to work, but it did look like it would. I wound up creating the following function to complete the test (with help from some posts in the Microsoft Knowledge base)

    Function TableExist(dbname As String, TName As String) As Integer

    'Determines if a table exists in the current database
    'Function returns true (-1) it table exists, false (0)if table does not exist

    'TName is the Name of the Table being tested

    Dim Db As DAO.database
    Dim Found As Integer ' Value is True (-1) if table exists
    Dim Test As String ' Use for Name of Table being tested

    Set Db = CurrentDb()
    Const NAME_NOT_IN_COLLECTION = 3265

    ' Assume table does not exist
    Found = False

    ' Trap for any errors
    On Error Resume Next

    'Test to see if table exists
    Test = Db.TableDefs(TName).Name
    If Err <> NAME_NOT_IN_COLLECTION Then Found = True

    If Found = True Then
    MsgBox Found, , "table found"
    Else: MsgBox Found, , "TABLE NOT FOUND"
    End If

    ' Reset error variable
    Err = 0

    TableExist = Found

    End Function
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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