How to have a Macro run every hour? 1

[scada101]

I have a macro that Outputs a table from acc2000 to an Excel spreadsheet. How can I automate the Macro to run once an hour?
Thanks

 

[tlbroadbent]

If the database will be open all day, you can create a form and set the Timer Interval. Then execute the macro from the Timer event at the appropriae time.

If the database will not be open all day, you could use Windows scheduler to start Access every hour. Use the command line option (/x macroname) to execute the macro.

The 2nd method is the better solution of the two in my opinion. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[scada101]

Thanks tlbroadbent,
Your suggestion worked. I used the scheduler idea.
However, it presented a new problem,overcoming the "overwrite" prompt has become the issue at hand. I need this file to write and overwrite every time the macro runs.This spreadsheet is a file for the customer to download via the internet, and the data updates every hour.

Any help will be appreciated.

 

[tlbroadbent]

I'm not sure what you mean by overwrite. If you mean you want to replace the data, just add a RunSQL action in the macro to delete all records in the table before importing. Or add DeleteObject action to delete the table if you want to recreate the table each time.

Make sure you have a SetWarnings action set to NO at the beginning of the macro and set to Yes at the end of the macro so the process doesn't wait for someone to click a button. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[scada101]

Thank you tlbroadbentfor your response,

I apologize for not making myself very clear. Allow me to give you a brief topology of my setup. I have an OPTO 22 data aquisition system in the field.

This program writes data to acc97. It will not write to acc2000, therefore I have tables in acc2000 that are linked to the tables in acc97. These links are dynamic and some of them refresh every two seconds. This portion works very well.

In acc2000 I use a macro to "output" the contents of the tables to an Excel spreadsheet. This spreadsheet is "downloadable" to customers via the internet.

When the macro runs, Excel opens and I get an "OK to overwrite" prompt. This is the barrier that I have run up against. I hope this clarifies my situation.

 

[tlbroadbent]

How are you exporting? If you are using the OutputTo Action then I don't kow how to suppress the message. I use the macro Action TransferSpreadsheet and don't get that message. Access just overwrites the file.

If TransferSpreadsheet doesn't meet your needs then you could write a function to delete (kill) the file and execute the function before exporting. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[scada101]

Thank you tlbroadbent!!!

Your suggestions solved my problem. I went with the "transfer spreadsheet" command and timed event in a form to execute the macro.

Again, I wish to express my thanks.
scada 101