I need to delete a table

[Jon123]

Is there a way to delete a table using code. Not just records I want to delete the table.
jon

 

[jdraw]

Is there a way to delete a table using code. Not just records I want to delete the table.
jon

Here's a good place to start.
http://www.sqlcourse.com/drop.html

Post back if you need more info.

 

[RuralGuy]

From VBA Help:
DoCmd.DeleteObject acTable, "Former Employees Table"

 

[Jon123]

RuralGuy that is what I was looking for. How can I check to see if the table is there before I delete it.

 

[RuralGuy]

Do you really care? Just ignore any error returned. On Error Resume Next until the line executes and then turn them back on.

 

[Jon123]

right so it would be

DoCmd.SetWarnings False
On Error Resume Next
DoCmd.DeleteObject acTable, "Wetclean Checklist"
DoCmd.SetWarnings True


is this correct?

jon

 

[RuralGuy]

I would suggest:

DoCmd.SetWarnings False
On Error Resume Next
DoCmd.DeleteObject acTable, "Wetclean Checklist"
DoCmd.SetWarnings True
On Error GoTo YourOriginalErrorHandlingLabel

 

[Jon123]

thanks for the help

 

[dallr]

There are many ways to check to see if a table exists. I would show you one method.

Public Function TblExists(strTableName As String) As Boolean

'ADO Method

Dim obj As AccessObject
Dim dbs As Object

Set dbs = Application.CurrentData

For Each obj In dbs.AllTables
    If obj.Name = strTableName Then
        TblExists = True
        Exit For
    End If
Next obj

End Function

Although the method RuralGuy mentioned will work sometimes you will have other code running in the same module and you will not want to use "On Error Resume Next", but actually check for the table before deleting it.

Cheers.

Dallr

 

[Jon123]

so how do I put this code in the command button?

jon

 

[dallr]

Put the code in a module and then call it on your button as follows.

Dim tbl as string 

tbl = "YourTableName"
if TblExists(tbl) then 
'.....place your code here to delete the table 
End if

Dallr

 

[editolis]

Also you can use something like this:

sub DeleteTable(sTable as string)
dim tbl as dao.tabledef
for each tbl in currentdb.tabledefs
if tbl.name=sTable then
currentdb.execute "drop table [" & sTable &"]"
end if
next
end sub

 

[dallr]

Yep there are many ways to do this....

I would also place a exit For within the IF statement so you do not have to loop through all the remaining tables after you find a match.

Dallr

 

[Jon123]

Got it

thanks everyone for the help

jon

 

[dallr]

Glad to help

 

[raskew]

Hi -

The DoCmd.DeleteObject actable, "tblName"

will not work if the table named is in a relationship with other table(s).

Realize the following looks like overkill, but it works:

Sub DeleteTableTest3(pMyTable As String)
' This procedure deletes specified tables and any
' existing relationships the tables are participating
' in.
' Used inappropriately, it will have a devastating
' effect on an application.
' For safety's sake, I've commented-out the actual
' commands (they follow debug.print in every case).
' Once you've examined the code and are ready to go,
' remove the comments (and you may want to 
' comment-out the debug.print commands).

Dim db      As Database
Dim td      As TableDef
Dim Test    As String
Dim tName   As String
Dim thisrel As Relation

    Set db = CurrentDb
    
    On Error Resume Next
    
    tName = pMyTable
    '**********
    'Does table tName exist?
    'If true, delete it;
    '**********
    
      Test = db.TableDefs(tName).Name
      If Err <> 3265 Then
         '**********
         ' Since the delete action will fail if the
         ' table is participating in any relation, first
         ' find and delete existing relations for table.
         '**********
         For Each thisrel In db.Relations
            If thisrel.Table = tName Or thisrel.ForeignTable = tName Then
               Debug.Print tName & " | " & thisrel.Name
               'db.Relations.Delete thisrel.Name
            End If
         Next thisrel
         '**********
         ' Now, we're ready to delete the table.
         '**********
         Debug.Print tName & " will be deleted"
         'docmd.SetWarnings False
         'docmd.DeleteObject acTable, tName
         'docmd.SetWarnings True
      End If

    db.Close
    Set db = Nothing
End Sub

HTH - Bob

 

[RuralGuy]

And another fine piece of code appears from the Ozarks. Thanks Bob.