How to Delete a Table from a Database Using Visual Basic

ID: Q110959

The information in this article applies to:

Microsoft Visual Basic Professional Edition 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

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

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

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 query words: 3.00

Keywords :
Version :
Platform :
Issue type :