By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Scheduling a Daily query to run at midnight

P: 35
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
Feb 19 '07 #1
Share this Question
Share on Google+
9 Replies


P: 35
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!
Feb 19 '07 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Feb 19 '07 #3

P: 35
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! :)
Feb 19 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Open(Cancel As Integer)
  5.   Call DoCmd.SetWarnings(False)
  6.   Call CurrentDb.QueryDefs!MyQuery.Execute
  7.   Call DoCmd.SetWarnings(True)
  8. End Sub
Remember, scheduling as standard, uses the SYSTEM account which is fine to run things locally but doesn't get network access.
Feb 20 '07 #5

P: 35
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!!
Feb 22 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
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 :()
Feb 23 '07 #7

nico5038
Expert 2.5K+
P: 3,072
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)
Feb 25 '07 #8

P: 1
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
May 15 '07 #9

niftynev
P: 1
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.
Jul 23 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.