Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
87 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 1000 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 23

Assisted Solution

by:Ferruccio Accalai
Ferruccio Accalai earned 500 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 40

Assisted Solution

by:PatHartman
PatHartman earned 500 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 40

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

773 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