need macro to run every 5min?

[teeitup]

I've setup a macro to extract data to an Output File, send and post that data automatically to my companies web site. This macro works great, with the exception of the annoying "do you want to replace the existing file?," message, whick I'm working on eliminating.

Now, I need to execute this macro every 5min. This database can be updated by the minute and our website must reflect the latest changes to the database. Hence, my need for executing the macro every 5mins.

Windows scheduler does not support minutes only days. I tried to use a Freeware scheduling utility that supports minutes, but when it executes the macro, the database and macro elements are not found. Perhaps I need to create a batch file of some sort. But if I need to, I"ll explore this in more detail.

But...the best solution for me, is to somehow within access, schedule this macro to run every 5min. The database will always be open when my macro runs. Has anyone ever used Access to perform something similar?

I've browsed through this forum, but I have not found a post that is similar to my needs.

Thank you in advance for your help and replying so quickly.

Tee --

 

[pbaldy]

Forms have a timer event, which could be used to execute your macro every five minutes (set with Timer Interval). The form would have to be left open, but could be hidden. The code would be:

DoCmd.RunMacro "MacroName"

I'd suggest converting the macro to code, but that's me. Look at those and post back if you need help.

ps. Wish I could tee it up, but there's too much snow.

 

[The_Doc_Man]

I'm with pbaldy on the proposed solution and on the "convert to code" issue, too. There is a function to do that conversion on the menu bar, I think. At least, there is when you are on the Macro panel.

You have to run VBA to do the timer stuff anyway, and if you convert the macro to code, you get better error handling capabilities even if you don't use them right away.

BTW, timer units are milliseconds, so 5 minutes is 300,000 ticks on your form timer. Also, once the Form_OnTimer event fires, you have to reload the form's .Timer property because timers are single-shot.

 

[pbaldy]

... once the Form_OnTimer event fires, you have to reload the form's .Timer property because timers are single-shot.

Can you elaborate on this Doc Man? I use the timer event in a couple of applications, and it seems to work fine without this. Is it a version difference (I have A2k)?

 

[teeitup]

thanks for replying guys. I will explore the "convert to code," option to utilize the timer.

That sounds like my best solution, so far.

If you can think of anything else or additional tips, please let me know.

Tee --

 

[FoFa]

Might it not be easier to just have your web page pull the data from your DB?
Can you wrap the upload process around the update process?

 

[teeitup]

Negative FOFA....the web page is just displaying the HTML file, I extracted from my DB. The two applications are totally seperate and were not designed to interface with one another.

I'm creating the interface, by building this macro.

Tee --

 

[pbaldy]

I will explore the "convert to code," option to utilize the timer.

And just to clarify, you don't need to convert the macro to code to be able to use the timer event. You can use the code I provided to run the macro. We're just expressing our very strong preference and recommendation to use code rather than macros as a general rule. I don't use them at all.

 

[teeitup]

yo gentleman...the timerinterval is not working. I'm not doing something correct, I'm sure.

I've opened the FORM for the database. Under Events, on the "On Open" is where I want the macro to start running. Do I select the macro on the pull down menu, then within the macro, I activate the TimerInterval setting? This is what I've done, something is not right on the macro, it's like the expression builder, did not build the syntax correct. I keep getting the "OLE Object you referenced is not an OLE object." The messege references the TimerInterval setting.

Please explain how I set this up, in more detail. Thank you in advance.

Also...that darn "replacement existing file (y/n)" messages is still coming up, no matter what I do.

Tee --

 

[pbaldy]

The timer interval has nothing to do with the Open event. As mentioned earlier, you want the "On Timer" event, plus you want to set the interval to 300,000. The VBA code in the timer event will be:

DoCmd.RunMacro "MacroName"

I suppose you can just choose the macro from the dropdown list. As I don't use macros, I'm not sure. In any case it would be the Timer event, not the Open event. I wouldn't change the macro at all. You set the timer interval directly under the timer event in the form's properties.

 

[teeitup]

pbaldy,

Got it working. Thanks.

Yes...under the Forms/DesignView/Events/Data, on the "On Open," (when FORM opens) I select my Macro (from the pulldown) for the "On Timer" parameter, then I set the interval for 300,000.

It works great!

Thanks again.

 

[MichaelJoyce]

Warning Message

I didn't notice an answer to your issue with the Replace data warning message so I thought I'd post a possible solution.

The first line of your macro should be
SetWarnings No
The last line should be setwarnings Yes

You can do this in the VBA code you use to run the macro as well

docmd.setwarnings false
docmd.runmacro
docmd.setwarnings true

Hope this helps.

m

 

[RAJPUROHIT11]

Ans to your first issue :to elminate the warning message, asking you to confrim if you want to replace the file
you

 

[RAJPUROHIT11]

Ans to your first issue :to elminate the warning message, asking you to confrim if you want to replace the file
you need to create a from with a comand button on it this button will be joined with the macro with gives the output. if you click this button when the form is in form view the warning msg wont come.
Ans to your second issue:- I am currently doing a similar thing i can give you a .exe file to perform the function. or if you wish you can download a freeware called autohotkey from internet and install it and write a code.