Advent Day 22 Ms Access Gurus
Home Code

VBA > SQL > Drop a Table

Delete a table using DROP TABLE in SQL.

Screen shot

To delete a table in the current database, simply send the table name to DropTheTable.

Examples


delete table in current database from code:

Call DropTheTable("MyTable")

delete table in another database (see code for DeleteTableOtherDatabase):

Call DropTheTable("MyTable", db)

Logic

If a database is passed, use it. Otherwise, use the current database.

Set up error handler. Skip "Table does not exist" error.

Execute SQL to DROP TABLE for the specified table name.

If the table is in the current database then RefreshDatabaseWindow so its name disappears from the navigation pane.

Parameters

Optional:

Code

'*************** Code Start *****************************************************
' Purpose  : Delete a table from the current or an external database
' Author   : crystal (strive4peace)
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'-------------------------------------------------------------------------------

'                              DropTheTable
'-------------------------------------------------------------------------------
' 
Public Sub DropTheTable(psTableName As String _
   , Optional pdb As  DAO.Database) 
's4p 170416
'Delete a table
'if the table is not there to delete, no error is returned since there is nothing to delete.

   Dim sName As String
   Dim db As DAO.Database
   
   On Error GoTo Proc_Err
   
   'set the database object
   If pdb Is Nothing Then
      Set db = CurrentDb
   Else
      Set db = pdb
   End If
   
   With db
      'See if the table is there
      sName = .TableDefs(psTableName).Name
   
      'If no error then table is there -- delete it
      .Execute "DROP TABLE [" & psTableName & "];"
      .TableDefs.Refresh
   End With

   If pdb Is Nothing Then
      Application.RefreshDatabaseWindow
   End If
   DoEvents
   
Proc_Exit:
   On Error Resume Next
   Set db = Nothing
   
   Exit Sub
   
Proc_Err:

   Select Case Err.Number
   Case 3265 'Table does not exist
   Case Else
      MsgBox Err.Description, , _
         "ERROR " & Err.Number _
         & "   DropTheTable"
   End Select
   
   Resume Proc_Exit
   Resume 
End Sub
'-------------------------------------------------------------------------------

'                              DeleteTableOtherDatabase
'-------------------------------------------------------------------------------
' 
Public Sub DeleteTableOtherDatabase()
   Dim db As DAO.Database
   Set db = OpenDatabase("C:\path\MyDatabase.accdb")
   Call DropTheTable("MyTable", db)
   db.Close
   Set db = Nothing
   MsgBox "Done"
End Sub

' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

Share

Share with others ... here's the link to copy:
secure: https://MsAccessGurus.com/VBA/Code/Sql_DropTheTable.htm
or
unsecure: http://MsAccessGurus.com/VBA/Code/Sql_DropTheTable.htm

Reference

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / Database / Method / sExecute method

Help: Database.Execute method (DAO)

Do you have something to say or share?

It is interesting to hear from you. Was something not clear? Did you find a bug? Is an explanation wrong or not sufficient? Do you want the code do more (there is always more)?

Some of you write to say thanks and tell me what you're doing with Access ... its nice to get an echo back. I want you and others to be good with Access, and other Office applications like Excel, Word, and PowerPoint ... and Windows. Take advantage of the strengths in each to manage your information wisely.

Are you a developer? Do you want to share? Email to ask about getting your pages added to the code index.

When we communicate, collaborate, and appreciate, we all get better. Thank you. Email me at info@msAccessGurus.com

Goto Top