charlieb01
asked on
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(daTest
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
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(daTest
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.A
- 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.
- Reference Microsoft Access in the COM references of your .NET application
- Create a Microsoft.Office.Interop.A
- 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.
ASKER
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?