Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Does a table exist (Office 2K)

    Is there an easy function to determine whether a table exists?

    I want to delete a table with the line in an OnClick property:

    DoCmd.DeleteObject acTable, "Test Procedures"

    That works fine, but there are certain circumstances (i.e. program blew up later in the procedure) where this temporary table has already been deleted. I keep going back and commenting out the line above after debugging the problem that caused the blowup. Then I have to remember to un-comment the line.

    I have decided to delete the temp table at the end of the procedure, not at the beginning, but I will still have to comment it out during the debug process.

    I thought there was something like:

    If TableExist ("Test Procedures") Then

    and I'm pretty sure I've used it before, but it's not working here. Are my references set incorrectly?

    Any ideas?
    Kathi

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

    Re: Does a table exist (Office 2K)

    An easy way out is to disable error handling temporarily:

    On Error Resume Next
    DoCmd.DeleteObject acTable, "Test Procedures"
    On Error GoTo 0 ' or to your error handler

    You can also create a function in a module:

    Public Function TableExist(TableName As String) As Boolean
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    For Each obj In dbs.AllTables
    If obj.Name = TableName Then
    TableExist = True
    Exit For
    End If
    Next obj
    End Function

    and use it the way you describe

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Does a table exist (Office 2K)

    Perfect!! Why am I not surprised? Hans ROCKS!!! Thank you, yet again, my friend

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

    Re: Does a table exist (Office 2K)

    Another way is to specifically catch the error that occurs when the table does not exist.

    So in your error handler :

    if Err.number = 7874 then
    Resume Next
    else
    msgbox Err. Description
    etc

    Error 7874 is the one that occurs when you try to delete something that does not exist.
    Regards
    John



  5. #5
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Does a table exist (Office 2K)

    Good suggestion, John. Out of curiosity, how does one know the actual error numbers? Are they listed in a help index, or have you just run into this situation before?

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

    Re: Does a table exist (Office 2K)

    I did find a list of all the error codes once, but I think I have lost it now.

    In this case, I have a situation identical to yours. I use temp tables, and need to delete them. Sometimes they did not exist, so Access through err.number 7874.

    I make sure that error handlers tell me the number (err.number) as well as the description, so it is easy to handle special cases like this.
    Regards
    John



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

    Re: Does a table exist (Office 2K)

    Although you could list them all, the number of error messages is very large, and many of them are incomprehensible outside their context.

    What I usually do is report error numbers and messages during development and testing, e.g.

    Sub Test()
    On Error GoTo ErrHandler
    ...
    ...

    ExitHandler:
    Exit Sub

    ErrHandler:
    MsgBox "Error " & Err & " with description: " & Err.Description & " occurred."
    Resume ExitHandler
    End Sub

    Note the error numbers that occur. You can then handle these in a Select Case statement:

    ...

    ErrHandler:
    Select Case Err
    Case 2501
    ' Action canceled, exit without error message.
    Resume ExitHandler
    Case 7874
    ' object not found - continue
    Resume Next
    Case Else
    ' Report other errors
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Select
    End Sub

  8. #8
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Does a table exist (Office 2K)

    I believe that I will ALWAYS follow the error handling you have both suggested from this day forward. I've never even really worried about error handling thru code, just tried to think of everything that COULD go wrong and try to account for it - shameful. You know the phrase, "Make it idiot-proof and they'll make a better idiot!"

    I had to learn Access by myself back in the early 90's. Noone I knew had ever even heard of the program and my local bookstores/libraries were of no help. In two weekends I was able to produce my most complex application to date just by wading thru the sample apps. Yes, I was immensely proud of myself (and probably couldn't do it again today), but in the ensuing years I have realized again and again how poorly I do this and how very, very much I don't know or understand.

    I am forever grateful to you people and your patience.

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does a table exist (Office 2K)

    >how does one know the actual error numbers?

    Just for folks' future reference, there is a list of Error Numbers at <!mskb=146864>Microsoft Knowledge Base Article 146864<!/mskb>.

    HTH
    Gre

Posting Permissions

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