VB/Access -- If Table Exists, Delete It

[jwruble]

As a part of my application, I need to delete a table from an Access database. I have no problem doing that with the following code:

DoCmd.RunSQL "DROP TABLE myTable;"

However, depending on how far into the application the user is, that table may or may not exist. If it doesn't, the above statement throws the error "Table 'myTable' does not exist".

I have tried using SQL statements such as IF EXISTS to try to build a query that will first check for the existence of the table, and then delete it or not. But the IF EXISTS statement causes errors in the RunSQL method.

Does anybody have any suggestions on how to first check if a table exists, and then delete it (or not).

 

[ETID]

use an error handling routine to trap the error by err value

i.e.
sub my_sub()
on error got err_hit

'your code here

exit sub
err_hit:
if err = xxx then
resume next
else
msgbox("error "&cstr(err)&" encountered")
end sub

 

[JerryKlmns]

jwruble,

An example that works but doesn 't care if the table exists or not!

On Error Resume Next
Docmd.DeleteObject acTable, "YourTableName" 
On Error GoTo 0

 

[fredericofonseca]

    Dim dbase As Access.Application
    Dim i as Long
    Set dbase = New Access.Application
....
    For i = 0 To dbase.CurrentDb.TableDefs.Count - 1
      If dbase.CurrentDb.TableDefs(i).Name = sTable Then
        dbase.CurrentDb.TableDefs.Delete sTable
        dbase.CurrentDb.TableDefs.Refresh
        Exit For
      End If
    Next i

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RoyVidar]

For the fun of it, here's a thread with event more options of checking whether a table exists thread705-933401;-)

Hint - there are seven fora dedicated to Access (check forum search at the top)...

Roy-Vidar

 

[DaOtH]

Was just about to to type up something similar, maybe more useful as a general function just to check existence of a table.

Public Function pfTableExists(strTableName As String) As Boolean
Dim i As Integer
i = 0
pfTableExists = False
    While pfTableExists = False And i < CurrentDb.TableDefs.Count - 1
        pfTableExists = (CurrentDb.TableDefs(i).Name = strTableName)
        i = i + 1
    Wend
End Function

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[jwruble]

Wow, quite a number of great solutions. Thanks to all of you. As of now, I'm using the short snippet of code posted by JerryKlmns, since I just want the table deleted and don't really care whether the table exists or not. I'm actually deleting several tables at the same time, so I just simply used:

On Error Resume Next
DoCmd.DeleteObject acTable, "table1"

On Error Resume Next
DoCmd.DeleteObject acTable, "table2"

On Error Resume Next
DoCmd.DeleteObject acTable, "table3"

On Error GoTo 0

So thank you JerryKlmns for the very simple solution, and thank you all for the input!

 

[PHV]

And even shorter:
On Error Resume Next
DoCmd.DeleteObject acTable, "table1"
DoCmd.DeleteObject acTable, "table2"
DoCmd.DeleteObject acTable, "table3"
On Error GoTo 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jwruble]

Yep, very good call. I had added all of the resume next statements because for some reason I could only get the first table to be deleted (and of course I was wrong). I don't know for sure, but I think the problem was that some of the tables were still linked to open recordsets. It seems to work now that I make sure all recordsets are closed before trying to delete.

 

[JerryKlmns]

Don' t thank me, you should thank Tek-Tips Forums where I've learned just like you ....