How to Delete a Table from a Database Using Visual Basic

- 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.

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.

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.