Deleting Records using VBA Module

cms370

Registered User.
Local time
Today, 10:46
Joined
Jun 9, 2010
Messages
32
What is the recommended method for deleting records using a VBA module? Currently I am using an SQL method that specifies the table name and the upper time stamp date from which values should be deleted.

My database is continually being updated with new files from this same VBA module. I would like to delete old records based on a rolling window (say 30 days). Would this be the best way to go? It seems a bit slow...but then again my database contains 1 million + records.

Thanks.
 
Is your question more on how to trigger the delete process?
To delete records is quite simple and efficient ( too efficient!) and your time solution is simple and works.

If an event, tied to a relevant Form's control, say on open, called on the query to run then you could be deleting records many times a day and thus the task would not take long each time.

Some advice on deleting records recently has been to archive them to another Database rather then delete.
 
No, not how to trigger the process. I will have the process either manually started or set on some type of schedule.

Currently, as I am working my way through this (and yes, I am very new to Access) I am testing this database "file maintenance" process manually. Sometimes it takes up to 5-10 minutes to complete the record delete routine. I assumed it would have been faster than this to delete records based on a condition...but I could be wrong. Just not sure if there is a faster/better way to do things.
 
Afaik, running a query is as good as it gets. The performance gain will be made by making sure you've indexed your table appropriately. See here.
 

Users who are viewing this thread

Back
Top Bottom