Printable View
I want to limit the data in my database to 10000. Any records added after that, I want to delete the oldest records. Please let me know if I using the right code.
iRecords = rs.RecordCount
If iRecords > 50000 then
iDelete = iRecords - 50000
rs.MoveLast
for i = 1 to iDelete
rs.Delete adAffectCurrent
rs.MovePrevious
next
End If
Be careful using the code that you posted, as the functionality may differ based on your database type and sort order of your data.
In Oracle, if you do not specify your sort order, it could be different every time you run the same query, so you may not be deleting the rows that you really want to delete. You probably want to sort by the date added in order to make sure you are deleting the oldest rows. Your example indicates that you would be performing a reverse sort order on the date added, which is fine since you are using .movelast and .moveprevious (I think I would try using .movelast a second time instead of .moveprevious, but either way test it thoroughly to make sure you're deleting what you want to.)
As long as your sort order is accurate your code should work fine.
Hope this helps,
Kymberlie
This code might not work if you sort field by index. In this case new records are not at the end of the table.
Iouri Boutchkine
[email protected]