Solved: Test if tables exist

[nat1]

Hi,

Is it possible to check if a table exists and if so delete it with vba? I have looked at the IsLoaded property of the accessobject, but can't figure out how to implement it.

Thank-you for any help.

[matthewspatrick]

Make sure you have a reference to DAO...



Function DeleteTable(TableName As String)

' returns:
' 0 = table not found
' 1 = table deleted
' 2 = table found but not deleted


Dim td As DAO.TableDef
Dim TableExists As Boolean
Dim DidDelete As Boolean

On Error Resume Next
Set td = CurrentDb.TableDefs(TableName)
If Err = 0 Then
TableExists = True
Else
Err.Clear
TableExists = False
DeleteTable = 0
Exit Function
End If

td.Delete
DidDelete = (Err = 0)

If DidDelete Then DeleteTable = 1 Else DeleteTable = 2

End Function

[nat1]

Patrick,

Thank-you very much for the code, it's very much appreciated. I will give it a go at work tomorrow.

Regards,

[nat1]

Patrick,

I tried the function today at work, but encountered a couple of problems. I could only find a reference to Microsoft Data Access components, which I checked.

Then when I called the function from the OnOpen event of my form, I received an error.... The expression you entered has a function containing the wrong number of arguments.

Any ideas what I may have done wrong or missed?

Thanks

[matthewspatrick]

Sorry, try Microsoft DAO (my most recent version is 3.6).

[nat1]

Right I now have Microsoft DAO 3.6 installed and I still receive the error.

Thanks

[DarkSprout]

The above is a little messy, try using:
BooleanResult = DCount("*", "MSysObjects", "[Type] = 1 AND [Name] = '" & TableName & "'") > 0


And wrapped in a function, which can delete:=
Public Function TableExists(ByVal strTable As String, Optional DeleteIfFound As Boolean= False) As Integer
TableExists = DCount("*", "MSysObjects", "[Type] = 1 AND [Name] = '" & strTable & "'") > 0
If TableExists And DeleteIfFound Then
DoCmd.RunSQL "DROP TABLE [" & strTable & "]; "
End If
End Function

[nat1]

Darksprout,

Thank-you for the code works flawlessly. Can I place the function in a module and call it from my form (vba)?

Thanks again.

[DarkSprout]

Yup!
:friends:

[nat1]

Just like to say thank-you to Patrick and Darksprout for your help. I now have this set up to run whenever the form is opened.