Solved

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

Posted on 2016-10-18
6
67 Views
Last Modified: 2016-10-26
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
0
Comment
Question by:Victor Nares
6 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41849142
I'd use the forms timer event to run vba to "click" that button every five minutes.


Kelvin
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 250 total points
ID: 41849149
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
 
LVL 22

Assisted Solution

by:Ferruccio Accalai
Ferruccio Accalai earned 125 total points
ID: 41850029
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 41850464
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
 

Author Closing Comment

by:Victor Nares
ID: 41857204
Thank you all for the help. Was not aware of the table size issue MSAccess.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 41860890
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question