Command button to Delete 2 tables 1

[cimoli]

I would like to know how to make a Command Button automatically delete 2 tables. Extra credit if I can have an "Are You Sure? kind of deal inside the command button too.

I have a month routine where by I import a table from another Office of ours. The table imports as tblCustInfo1 into the HDQ Access 2010 file. I have a routine that makes a new table off of this one. It is called TomCustInfo. When my work is done, I want to automatically delete tblCustInfo1 and TomCustInfo.

I tried something from a 2003 tek-tips article. but I get an error.

The following is on the ON CLICK line.

Private Sub cmd8910_Click()

Dim Db As DAO.Database
Dim tbl As DAO.TableDef
Set Db = CurrentDb()

For Each tbl In Db.TableDefs

If tbl.Name = "TomCustInfo" Then
DoCmd.DeleteObject acTable, "TomCustInfo"

Else
End If
Next

For Each tbl In Db.TableDefs

If tbl.Name = "tblCustInfo1" Then
DoCmd.DeleteObject acTable, "TblCustInfo1"

Else
End If
Next

End Sub

 

[PHV]

but I get an error
Well, which error and where ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cimoli]

Oops. When trying to get 2 tables erased inside 1 command button, the error says:

"Run time error '2387'. You can't delete the table tblCustInfo1; it is participating in 1 or more relationships."

The following is noted in Yellow:
DoCmd.DeleteObject acTable, "tblCustInfo1"


By the way, if take out the following towards the end,
the buton works fine and deletes the table TomCustInfo. Although i wish i had a message that says,something like, you are about to delete TomCustInfo, yes or no.

(i took this part out and the command button erases tomcustinfo)

For Each tbl In Db.TableDefs
If tbl.Name = "tblCustInfo1" Then
DoCmd.DeleteObject acTable, "TblCustInfo1"
Else
End If
Next

tblCustInfo1 is not part of my relationships. My plain tblCustInfo is part of the relationships.
The table with the "1" at the end came from an import like i said.

thanks for checking this. If we can fix, this will allow me to ensure that i clean up my unwanted tables at the end.

 

[Andrzejek]

For an Extra credit

If vbYes = MsgBox("Are you sure?", _
    vbYesNo + vbQuestion + vbDefaultButton2, _
    "Delete Tables?") Then
[green]
    'Your Delete code goes here....
    [/green]
End If

But what will happen when User says: No? Your tables will not be deleted.

Have fun.

---- Andy