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
57 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 35

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 35

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

825 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