You are currently offline, waiting for your internet to reconnect

How to Delete a Table from a Database Using Visual Basic

Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Summary
This article describes how to delete a table from a database using theProfessional Edition of Visual Basic version 3.0 for Windows.

This technique works for any database that is in the native MicrosoftAccess database format. With slight modifications, it will also work withnon-Microsoft Access databases.
More information
To delete a table from a Microsoft Access database in Visual Basic, use anyof 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 alsodeleted. If you want to preserve the data in the table you are going todelete, write a Visual Basic application to copy the data to a new tablebefore deleting the existing table.

If you want to delete all the records in a table and still preserve theTableDef table definition, you can use the Execute method to do an SQLDelete 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 byopening the TEST.MDB database with the Data Manager provided with VisualBasic or with Microsoft Access.

The Database Object Hierarchy

At the top of the database object hierarchy is the Database object, not tobe confused with the Database property of the data control. One of theproperties of the Database object is the TableDefs collection, which isalso an object. The TableDefs collection represents all the individualTableDef objects associated with the Table objects, including any attachedexternal tables. The TableDef objects each represent the structure ormetadata of a table.

Each TableDef object consists of properties. For example, the Name propertygives you the name of the table. The Fields and Indexes properties of aTableDef object are collections of two additional data access objects, theField object and the Index object. For more information, see the VisualBasic Help menu.

More Examples of Data Access

The VISDATA.MAK project, which is installed in the VB3\SAMPLES\VISDATAdirectory, gives extensive examples of data access. The VISDATA sampleprogram uses every data access function in Visual Basic. Refer to theVISDATA source code for examples that show how to use each data accessfunction.
3.00
Properties

Article ID: 110959 - Last Review: 06/17/2014 21:46:00 - Revision: 2.0

  • KB110959
Feedback
> /html>="https://c.microsoft.com/ms.js"> 4050&did=1&t=">dy> var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" &t=">dy> var Ctrl = ""; document.write(" p;did=1&t=">om/c.gif?DI=4050&did=1&t=">