Delete all Rows in a table then append new rows to same table by automatically every 5 minutes.

Thank you all in advance.

I have a form which in turn has one button. That button launches a macro. The macro launches one Delete query and subsequently an append query. I would like to have the Macro that cycles the data (the macro that deletes an then appends) run every 5 minutes.

What would be the easiest way to make this happen given the MS ACCESS elements I've mentioned above?

It looks like vba code is the best way to make this happen, but Im at best a beginner at vba so please, feel free to provide some explanation around any vba code provided.

Thank you again!

-vn
Victor Naresdb DeveloperAsked:
Who is Participating?
 
Kelvin SparksConnect With a Mentor Commented:
The on Timer event (found the Event tab of the form properties I'd use

Call MyButton_Click()

Where MYButton is the name of the button to click. You'll also have to set the Timer Interval to 5 minutes. This is in milliseconds, so need to enter 300000 (300,000) for 5 minutes.

Once the form is open, this will then "click" your button every 5 minutes

Kelvin
0
 
Kelvin SparksCommented:
I'd use the forms timer event to run vba to "click" that button every five minutes.


Kelvin
0
 
Ferruccio AccalaiConnect With a Mentor Senior developer, analyst and customer assistance Commented:
I would not use the OnClick at all
Kevin is right about the timer but you could simply call the macro instead of click the button

Just to be clear
in form open
Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 300000 'value in milliseconds for 5 mins

End Sub

Open in new window

and the timer
Private Sub Form_Timer()
DoCmd.RunMacro "yourMacro"

End Sub

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
PatHartmanConnect With a Mentor Commented:
I hope this table isn't large because Access doesn't reuse the space occupied by the deleted records so this database will suffer from significant bloat during the course of a day and if the table is large, you might actually have to compact more than once during the day to recover the space occupied by the deleted records.

I would not recommend doing this with a Jet/ACE back end.  SQL Server would not have the same problem.
0
 
Victor Naresdb DeveloperAuthor Commented:
Thank you all for the help. Was not aware of the table size issue MSAccess.
0
 
PatHartmanCommented:
It's not table size per se.  It is the fact that the space occupied by deleted records is not recovered until a compact.  And just in case you think that using a Make Table query will solve the problem, it doesn't for the same reason.  Space is simply not reused.
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.