Does a table exist (Office 2K)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

>how does one know the actual error numbers?

Just for folks’ future reference, there is a list of Error Numbers at Microsoft Knowledge Base Article 146864.

HTH

Reply | Quote