Check For THen Delete Table[RESOLVED: THANKS PEET!!]

[SeanK]

I need to check to see if a certain table exists in an Access database, and if it does, delete it. I'm lost.

[Hack]

'posted by peet
'01/03/2002
Private Sub Command1_Click() 
Dim db As Database 
Dim sDBN As String 
Dim SQL As String 
 
sDBN = "C:\TEST.MDB" 
If TableExist(sDBN, "Table1") Then 
     Set db = OpenDatabase(sDBN) 
      SQL = "DROP TABLE Table1" 
      db.Execute SQL 
End If 
End Sub 
 
 
Private Function TableExist(sDBName As String, sTableName As String) As Boolean 
Dim i As Integer 
Dim db As Database 
 
On Error GoTo xit 
'open the database 
Set db = OpenDatabase(sDBName) 
'go through all the tables and check their names 
For i = 0 To db.TableDefs.Count - 1 
           If UCase(sTableName) = UCase(db.TableDefs(i).Name) Then 
                   TableExist = True 
                   Exit Function 
           End If 
Next i 
'If we get here ---> table doesn't exist 
TableExist = False 
Set db = Nothing 
Exit Function 
xit: 
TableExist = False 
Set db = Nothing 
MsgBox "The following unexpected error occured:" & vbCrLf & vbCrLf & Err.Number & " --> " & Err.Description, vbCritical 
End Function

[SeanK]

Thanks Hack..it worked like a champ (with some minor modifications to suite my needs!)

[Hack]

Don't thank me Sean, thank peet. He wrote/posted the code.

[Hack]

I hope peet sees this. He will love being thanked without ever, personnally, contributing anything.

He might even break out his cheerleader outfit!!

[peet]

sorry Hack, I don't think I have the strength of will to put away my only joy