I need to delete a table (1 Viewer)

Jon123

Registered User.
Local time
Today, 15:12
Joined
Aug 29, 2003
Messages
668
Is there a way to delete a table using code. Not just records I want to delete the table.
jon
 

RuralGuy

AWF VIP
Local time
Today, 13:12
Joined
Jul 2, 2005
Messages
13,826
From VBA Help:
DoCmd.DeleteObject acTable, "Former Employees Table"
 

Jon123

Registered User.
Local time
Today, 15:12
Joined
Aug 29, 2003
Messages
668
RuralGuy that is what I was looking for. How can I check to see if the table is there before I delete it.
 

RuralGuy

AWF VIP
Local time
Today, 13:12
Joined
Jul 2, 2005
Messages
13,826
Do you really care? Just ignore any error returned. On Error Resume Next until the line executes and then turn them back on.
 

Jon123

Registered User.
Local time
Today, 15:12
Joined
Aug 29, 2003
Messages
668
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

AWF VIP
Local time
Today, 13:12
Joined
Jul 2, 2005
Messages
13,826
I would suggest:
Code:
DoCmd.SetWarnings False
On Error Resume Next
DoCmd.DeleteObject acTable, "Wetclean Checklist"
DoCmd.SetWarnings True
On Error GoTo YourOriginalErrorHandlingLabel
 

dallr

AWF VIP
Local time
Today, 12:12
Joined
Feb 20, 2008
Messages
81
There are many ways to check to see if a table exists. I would show you one method.

Code:
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

Registered User.
Local time
Today, 15:12
Joined
Aug 29, 2003
Messages
668
so how do I put this code in the command button?

jon
 

dallr

AWF VIP
Local time
Today, 12:12
Joined
Feb 20, 2008
Messages
81
Put the code in a module and then call it on your button as follows.
Code:
Dim tbl as string 

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


Dallr
 

editolis

Panathinaikos Fun
Local time
Today, 21:12
Joined
Oct 17, 2008
Messages
107
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

AWF VIP
Local time
Today, 12:12
Joined
Feb 20, 2008
Messages
81
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
 

raskew

AWF VIP
Local time
Today, 14:12
Joined
Jun 2, 2001
Messages
2,734
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:

Code:
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
 
Last edited:

RuralGuy

AWF VIP
Local time
Today, 13:12
Joined
Jul 2, 2005
Messages
13,826
And another fine piece of code appears from the Ozarks. Thanks Bob.
 

Users who are viewing this thread

Top Bottom