Scheduling a Daily query to run at midnight

[tfurubay]

Hi,

I'm new to this forum. i'd like to setup a macro to run a query daily. Is there any way in access to do this?

thanks,

todd

[tfurubay]

Hi,

same poster. i figured out one way. thought this might be helpful to others so i am posting how i did this. kind of cumbersome but it does the trick!

create a macro called autoexec. this macro will run automatically everytime you open the database. setwarnings to off, then program your query and don't forget to setwarnings back to on!

then use microsoft scheduler and schedule your database to open.

"To open Scheduled Tasks, click Start, click All Programs, point to Accessories, point to System Tools, and then click Scheduled Tasks."

your security level may prompt you to authorize opening access. this means that you must acknowledge that you wish to open access. to avoid this, go to tools, macro in access then set security level to low.

this puts you ahead of the game every day!

[nico5038]

You can now join the team here to help others :-)

That would have been the solution I would have proposed too. The only problem might be that the system isn't active when the job is scheduled. The only "safe" way would be to use a server and start the database from a service, but that might be too "heavy" when you're sure that the system is running.

Success !

Nic;o)

[tfurubay]

i see what you mean. I'm not totally clear on the resolution you've proposed but if i do come accross that problem, i will be sure to come a calling!

would be glad to help others, thanks! :)

[NeoPa]

Rather than using a macro I would use a Form that you set to start automatically on Startup (Tools / Startup).
In the OnOpen event procedure for this form you can put VBA code to execute the query you want to run (An action query I presume).
Around that you can set warnings Off & back On again.

Option Compare Database
Option Explicit
 
Private Sub Form_Open(Cancel As Integer)
  Call DoCmd.SetWarnings(False)
  Call CurrentDb.QueryDefs!MyQuery.Execute
  Call DoCmd.SetWarnings(True)
End Sub

Remember, scheduling as standard, uses the SYSTEM account which is fine to run things locally but doesn't get network access.

[tfurubay]

that's definitely a more elegant solution! i don't have the IS background but perhaps one day...

but, i am one piece away from fully automating my job! i have created a macro to automatically send a report to a group of people. the macro or outlook has a security function that required a user to authorize any emails that access is trying to send. is there any way to turn this off?

thanks!!

[NeoPa]

that's definitely a more elegant solution! i don't have the IS background but perhaps one day...

but, i am one piece away from fully automating my job! i have created a macro to automatically send a report to a group of people. the macro or outlook has a security function that required a user to authorize any emails that access is trying to send. is there any way to turn this off?

thanks!!

Todd,

Not sure what IS is :confused: sorry.
As to the question about the e-mail, I suggest you post this in a separate thread. I have the same issues you do (not a problem for me particularly as it's manned not scheduled), so I may see what answers come out of that one.

BTW this is a good example of why it's a good idea, as one expert may respond to one question but be very little use on another (Me :()

[nico5038]

You can use a Docmd.SendObject statement to trigger an email and suppress the warning message. There's a parameter that can be set to control this.

Nic;o)

[stevenjones35]

Hi everyone,

I ran across an interesting program that can schedule macros to run in a MS Access database. It works by clicking a few buttons to set the date/time, then set the macro name to run. It doesn’t require using Windows Scheduled Tasks which is good for me. Check it out: http://macros.subpacket.com/

Regards,

Steven

[niftynev]

You can use a Docmd.SendObject statement to trigger an email and suppress the warning message. There's a parameter that can be set to control this.

Nic;o)

Hi Nic. I use Docmd.SendObject to periodically send email from Access using a macro, but I have to use an external 'ClickYes' utility to get past the security warning message in Outlook that alerts an external app is trying to send a message. It would be great if there is a parameter in the SendObject statement in my Access module that would suppress this or ignore it, or send it a click yes, but darned if I can find it by reference to the help files. Were you referring to the Edit Message parameter or is there something else available in SendObject? Thanks in anticipation.