Microsoft KB Archive/113390

= How to Delete All Records from MDB Database Table In VB 3.0 =

Article ID: 113390

Article Last Modified on 1/8/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q113390



SUMMARY
This article shows by example how to delete certain tables from a Microsoft Access database (.MDB file) by deleting the TableDef.

You could also use this method to delete all the records from a table by first finding the TableDef and then compacting the database. This method may be faster than:


 * Deleting all of the records one by one.
 * Using the action query Delete method.

The following example uses a four-step process to quickly delete all the rows in a table:


 * 1) Find a specific TableDef.
 * 2) Delete the TableDef.
 * 3) Compact the database.
 * 4) Add an empty TableDef back to the database.



MORE INFORMATION
NOTE: Before you try this example, you will need to go into the Data Manager. Choose Data Manager from the Window menu of the main Visual Basic menu. Below are the steps for using Data Manager to add a new sample table called newtb to use as the example table.


 * 1) In Data Manager, choose Open Database Access from the File menu. Double-click the BIBLIO.MDB database to select it. This will open the Microsoft Access database named BIBLIO.MDB.
 * 2) Click the New button.
 * 3) Enter newtb as the name for the new table.
 * 4) From the Table: newtb window, select the Add button in the Fields: section.
 * 5) Enter fld1 as the Field Name.
 * 6) Select the Field Type as Integer.
 * 7) Click the OK button.
 * 8) Close the Table: newtb window.
 * 9) You should see the newtb table name in the list.
 * 10) Exit from Data Manager.

Step-by-Step Example Showing How to Delete All Rows from a Specific Table
 Start a new project in Visual Basic. Form1 is created by default. Add one Label, one List box and four Command button controls to Form1.  Using the following table as a guide, set the properties of the controls you added in step 2.   Control     Property      New Value --  Label1      Caption       Use this method to delete a table or all the records in a table very quickly, instead of                            deleting the records one by one.

Command1    Caption      Press to view the TableDefs of BIBLIO.MDB

Command2    Caption      Press to Delete the selected 'newtb' TableDef of BIBLIO.MDB

Command2    Visible      False

Command3    Caption      Press to Compact BIBLIO.MDB to Delete the unwanted table's (newtb) records

Command3    Visible      False

Command4    Caption      Press to add an empty TableDef (newtb) back to BIBLIO.MDB

Command4    Visible      False

List1       Visible      False   Place the following code in the Command1 Click event procedure of Form1: Sub Command1_Click ' List the TableDefs in BIBLIO.MDB: Dim db As Database Set db = OpenDatabase("BIBLIO.MDB") For i% = 0 To db.TableDefs.Count - 1 list1.AddItem db.TableDefs(i%).Name Next i%     db.close list1.visible = True End Sub   Place the following code in the List1 Click event procedure of Form1: Sub List1_Click command2.Visible = True End Sub   Place the following code in the Command2 Click event procedure Form1: Sub Command2_Click ' Delete the newtb TableDef: Dim db As database Set db = OpenDatabase("BIBLIO.MDB") screen.MousePointer = 11 db.TableDefs.Delete "newtb"  ' Deletes the TableDef screen.MousePointer = 0 command3.Visible = True End Sub </li>  Place the following code in the Command3 Click event procedure of Form1: Sub Command3_Click ' Compact the database to get rid of the records: Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0" screen.MousePointer = 11 ' Next, compact the BIBLIO.MDB database under the name ' BIBLIO2.MDB (compare sizes). ' Enter the following two lines as one, single line: CompactDatabase "C:\VB\BIBLIO.MDB", "C:\TMP\BIBLIO2.MDB", DB_LANG_GENERAL, 2 screen.MousePointer = 0 command4.Visible = True End Sub </li>  Place the following code in the Command4 Click event procedure of Form1: Sub Command4_Click ' Create a new empty newtb table: Dim db As database Dim newtd As New TableDef Dim newidx As New index Dim f1 As New field screen.MousePointer = 11 Set db = OpenDatabase("C:\TMP\BIBLIO2.MDB") newtd.Name = "Newtb" f1.Name = "fld1" f1.Type = 3       ' Integer data type newtd.Fields.Append f1     newidx.Name = "Field1 index" newidx.Fields = "fld1" newidx.Primary = True newtd.Indexes.Append newidx db.TableDefs.Append newtd screen.MousePointer = 0 End Sub </li> From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program. Click the Command1 button. Select a TableDef referenced as 'newtb' in the List1 box. Click the Command2 button. Click the Command3 button. Click the Command4 button.</li></ol>

Additional query words: 1.00 2.00 3.00

Keywords: KB113390

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.