How to Delete a Table from a Database Using Visual Basic

Last reviewed: June 21, 1995
Article ID: Q110959
The information in this article applies to:

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

SUMMARY

This article describes how to delete a table from a database using the Professional Edition of Visual Basic version 3.0 for Windows.

This technique works for any database that is in the native Microsoft Access database format. With slight modifications, it will also work with non-Microsoft Access databases.

MORE INFORMATION

To delete a table from a Microsoft Access database in Visual Basic, use any of the following methods:

  • Open the database in the Visual Basic Data Manager, select the table, and choose the Delete button. You can run the Data Manager program from the Window menu in Visual Basic, or from the Windows File Manager (run DATAMGR.EXE in the Visual Basic directory). You can delete a table from any database type supported by Visual Basic.
  • Use the sample Visual Basic program listed below to delete a table using database object variables.
  • Open the database in Microsoft Access, select the table, and choose Delete from the Edit menu.

CAUTION: When you delete a table, all the data stored in that table is also deleted. If you want to preserve the data in the table you are going to delete, write a Visual Basic application to copy the data to a new table before deleting the existing table.

If you want to delete all the records in a table and still preserve the TableDef table definition, you can use the Execute method to do an SQL Delete command. For example:

   Dim db as database
   Set db=OpenDatabase("testing.mdb")
   db.Execute "Delete From BadTable"

Sample Program

  1. Start a new project in Visual Basic. Form1 is created by default.

  2. Add a the following code to the Form Load event:

       Sub Form_Load ()
    
          Dim db As database
          Dim tds As TableDefs
          form1.Show  ' Must Show form in Load event for Print to be visible.
          form1.WindowState = 2   ' Maximize Form1 to make room for table list.
          sourcedb = "c:\VB3\BIBLIO.MDB"  ' Original master database.
          destdb = "C:\TEST.MDB"     ' Path to database with table to delete.
          tabletodelete = "Authors"
          FileCopy sourcedb, destdb  ' Use copy of database; preserve original.
          Set db = OpenDatabase(destdb)
          Set tds = db.TableDefs     ' Open the TableDefs collection.
    
          ' Display names of all tables in database:
          For j = 0 To tds.Count - 1
             Print tds(j).Name
          Next
          Print
    
          ' Delete a table. (This deletes the TableDef and all records):
          tds.Delete tabletodelete
          ' or use:   db.TableDefs.Delete tabletodelete
    
          ' If you want to delete all records and still preserve the TableDef
          ' table definition, use the following instead of the above Delete:
          ' db.Execute "Delete From " & tabletodelete
    
          ' Display names of all tables in database:
          Print "List of tables after deleting one table:": Print
          For j = 0 To tds.Count - 1
             Print tds(j).Name
          Next
    
       End Sub
    
    

  3. Start the program or press the F5 key. The program lists all the tables in the database before and after deleting a table. Close the form to end the program.

You can also confirm that the table was deleted from the database by opening the TEST.MDB database with the Data Manager provided with Visual Basic or with Microsoft Access.

The Database Object Hierarchy

At the top of the database object hierarchy is the Database object, not to be confused with the Database property of the data control. One of the properties of the Database object is the TableDefs collection, which is also an object. The TableDefs collection represents all the individual TableDef objects associated with the Table objects, including any attached external tables. The TableDef objects each represent the structure or metadata of a table.

Each TableDef object consists of properties. For example, the Name property gives you the name of the table. The Fields and Indexes properties of a TableDef object are collections of two additional data access objects, the Field object and the Index object. For more information, see the Visual Basic Help menu.

More Examples of Data Access

The VISDATA.MAK project, which is installed in the VB3\SAMPLES\VISDATA directory, gives extensive examples of data access. The VISDATA sample program uses every data access function in Visual Basic. Refer to the VISDATA source code for examples that show how to use each data access function.


Additional reference words: 3.00
KBCategory: kbprg kbcode
KBSubcategory: APrgDataOther


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 21, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.