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

- Microsoft Visual Basic programming system for Windows, version 3.0

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.

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

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

2. Add one Label, one List box and four Command button controls to Form1.

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

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

5. Place the following code in the List1 Click event procedure of Form1:

      Sub List1_Click ()
         command2.Visible = True
      End Sub
   
   

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

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

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

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