How to Delete a Table from a Database Using Visual Basic
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.
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.
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.
: 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"
- 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 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.
Article ID: 110959 - Last Review: 06/17/2014 21:46:00 - Revision: 2.0