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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.