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
77 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 23

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 38

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 38

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 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