Delete all records in an Access database table from VB.Net

I have to clear out all old records from multiple MS Access tables each time my program begins (clearing out the data from the last run).

Each table can have as many as 15000 rows and the method I am using now is quite slow. Is there a better way to clear out the table than deleting one row at a time as I am doing in the code below?

Thanks,
Charlie

Here's my code:

Sub ClearMeasurementTable()

        Dim conn As New OleDbConnection(constr)
        Dim qry As String = "SELECT * FROM Measurement"
        Dim daTestTbl As New OleDbDataAdapter(qry, conn)
        Dim cbTestTbl As New OleDbCommandBuilder(daTestTbl)
        Dim dtTestTbl As New DataTable()

        Try
            daTestTbl.Fill(dtTestTbl)

            For Each dr As DataRow In dtTestTbl.Rows
                dr.Delete()
            Next
         
            daTestTbl.Update(dtTestTbl)
            dtTestTbl.Dispose()
            daTestTbl.Dispose()
        Catch ex As Exception
            MsgBox(ex.Message, vbOKOnly, "Clear Measurement Table!")

        End Try

    End Sub
charlieb01Asked:
Who is Participating?
 
Jacques Bourgeois (James Burger)PresidentCommented:
Sub ClearMeasurementTable()

            Dim conn As New OleDbConnection(constr)
            Dim cmd As New OleDbCommand("DELETE * FROM Measurement", conn)

            Try

                  conn.Open()
                  cmd.ExecuteNonQuery()
                  conn.Close()

            Catch ex As Exception
                  MsgBox(ex.Message, vbOKOnly, "Clear Measurement Table!")

            End Try

      End Sub

Do you know that you should also Compact your database from time to time. Deleting data simply marks it as being deleted. It does not remove it from the database until you trigger a Compact from the Tools menu or ribbon.
0
 
Saurabh Singh TeotiaCommented:
You can delete it one by go by using delete query which is this...

Sub ClearMeasurementTable()

        Dim conn As New OleDbConnection(constr)
        Dim com As OleDbCommand

com = new OleDbCommand("delete * from measurement",conn)

conn.Open()
com.ExecuteNonQuery()
       MsgBox("Data Deleted")
        conn.Close()

end sub

Open in new window

0
 
charlieb01Author Commented:
James,

That's another topic - and one I am interested in.  I was going to search to see if there was a command for compacting the database from VB.Net.
Any info?
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
I do not have Access installed anymore on my system, so I cannot give you the exact code, and the words I use might be slightly off. But it goes something as this:

- Reference Microsoft Access in the COM references of your .NET application
- Create a Microsoft.Office.Interop.Access.Application object
- That object will have a Compact method or something containing Compact in its name.

It does the job, but won't work in many situations. You have to carefully trap errors if you want to do it with code.

The first problem is that this is not a feature of the access database engine (JET or ACE) that you use when you connect with a .NET application, it is a feature of Microsoft Access, an application that is only an interface to the database engine, the same way that your application is. That means that the user must have Access installed on his computer.

Second, compacting does not work if one or more user is logged in when you call it. So, unless the database is used only sporadically or by one user, Compact usually needs to be run outside of office hours.

It is often easier done from time to time by some administrator, manually from Access itself. The command to compact is somewhere in the Tools menu.

In the days where I was still using Access, I had different ways of dealing with that, depending on the customer.

In a small office, I had a customer once who would regularly yell : "Everybody out of the database!", then compacted and then yelled "OK, you can go back in!".

At another place, where everybody was having lunch at the same time, it was mandatory to get out of the database before leaving for the cafeteria, making sure that the database could be compacted.

I sometimes created a small VBA Sub in the database and ran it during the night through Windows Task Scheduler.

At one place, the boss was the last one to leave at night. On his workstation, we made sure to set the option to Compact on Exit that was somewhere in the Access options dialog. It might be a good thing to do with everyone that is using Access. They won't get an error if they exit while somebody is still working on the thing, but the last one to leave will trigger a Compact.

Finally, compact does not only removes useless stuff, but it also defragment the database, which makes it more efficient.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.