How to check if a table exists 1

[pmo]

I am wanting to delete a table in my database if it exists.
I am able to delete the table if it is present but errors if it is not present.
My code so far is

Public Function DelTables()
strFile = "ASCCopyT"**********I need code here that identifies the table***************
If Len(strFile) > 0 Then
DoCmd.DeleteObject acTable, "ASCCopyT"
End If

Thanks for any help you can give.

Wayne

 

[ByteMyzer]

Try this:

Public Function DelTable(tblName As String)
    Dim tbd As TableDef
        For Each tbd In CurrentDb.TableDefs
            If tbd.Name = tblName Then
                CurrentDb.TableDefs.DELETE tblName
            End If
        Next
End Function

Call the function with:
[tt]Call DelTable("MyTable")[/tt]

...and it will delete the table named MyTable.

 

[pmo]

Thanks ByteMyzer,
I will give it a try. My knowledge of code is limited but this is of great assistance. Thanks

Wayne

 

[pmo]

ByteMyzer,
Still having trouble. Your help would be appreciated.
This is what I have at the moment:-

Public Function DelTable(ASCCopyT)
For Each tbd In CurrentDb.TableDefs
If tbd.Name = tblName Then
CurrentDb.TableDefs.Delete ("ASCCopyT")
End If
Next
End Function

I get an error that says ASCCopyT can not be found.

Wayne

 

[RoyVidar]

Try following ByteMyzer's instructions (or rather, do a copy paste of their code) and call it with:

[tt]Call DelTable("ASCCopyT")[/tt]

- if that's the name of the table you wish to delete

By the look of it, it should work on every table name you choose to pass to the function.

Roy-Vidar

 

[pmo]

Roy,
Thanks for responding. I was substituting "tblName" in the code for the table I wanted to delete.

All works now.
Thanks also ByteMyzer.

Wayne

 

[jadams0173]

I came across this code and this is exactly what I want to do however the code does not delete my table. This table is created by a make table query, could this be why. I did a cut and paste and the used

 Call DelTable ("mktJobsWithPics")

This table is only created if the user choses one of the option buttons so it is not created every time the DB is used.

When I watch the variables tbd.name I see my other tables and some "weird" table names "MSysAccessObjects","MsysACEs" "MsysObjects" and a couple others that start with Msys. One name MsysQuerys could this be the one I need to delete?

 

[cwhite23]

Don't delete any table that starts with 'MS'. These are System tables that Access needs to be able to function. Deleteing them will destroy your database!