473,593 Members | 2,885 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Scheduling a Daily query to run at midnight

35 New Member

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?


Feb 19 '07 #1
9 20734
35 New Member

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
3,080 Recognized Expert Specialist
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 !

Feb 19 '07 #3
35 New Member
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
32,566 Recognized Expert Moderator MVP
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
  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
35 New Member
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?

Feb 22 '07 #6
32,566 Recognized Expert Moderator MVP
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?


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
3,080 Recognized Expert Specialist
You can use a Docmd.SendObjec t statement to trigger an email and suppress the warning message. There's a parameter that can be set to control this.

Feb 25 '07 #8
1 New Member
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.subpacke t.com/


May 15 '07 #9
1 New Member
You can use a Docmd.SendObjec t statement to trigger an email and suppress the warning message. There's a parameter that can be set to control this.

Hi Nic. I use Docmd.SendObjec t 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

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

Similar topics

by: Slavyan | last post by:
Does anyone knows any good scheduling controls (like daily, weekly, monthly, etc.) that are specifically written for .NET. Maybe there are controls that comes with Visual Studio.NET or something. Or any third party ones? Thanks.
by: m3ckon | last post by:
Hi, I want to schedule a backup of three databases on a daily basis. I've written the code to run the 3 backups in TSQL and was wondering how best to automate this procedure? Should I put the code in an sproc and then schedule running that command in the DTS or should I just add the TSQL into a DTS command?? Or is there a better way of doing this???
by: Mike | last post by:
Normally scheduling a job is a very elementary operation but for some hidden reason I've been unable to schedule a job which runs on a 28 day cycle, even though I have at least 16 other jobs seperately scheduled to run on a 28 day cycle with different start dates/times. My production SQL server is running SQL Server Enterprise Edition 8.00.760(SP3) on a 2 Processor(4 virtual) Microsoft
by: Myth of Sisyphus\). | last post by:
I wish to run this script daily. Can this be scheduled? backup log shipmateDB with truncate_only G
by: Muscha | last post by:
Hi, In our application we need to have a high performance scheduling framework. We want to be able to say for item 21 do an update on such and such time. We will have around 1 million items of this kind with different scheduling time, so not all will be updated per hour. Some will be per hour some will be once every 2 weeks. Does anyone has any suggestion how to tackle this problem? Oh and one of the requirement is that we are not...
by: androoo | last post by:
Hi I would like some design advice for this problem. I host my website on a remote host and have an aspx page that searches daily for data and inputs data into the database. I run this page by going into it every day. I would like to have this page fire the event to search and input data on a daily basis at a certain time.
by: Jeff Young | last post by:
I am looking for documentation and sample code to create an outlook like calendar. I need to display 3 views(daily, weakly and monthly). Also msdn told me if I display certain information they guarantee a response with in 24 hours anyone remember what information that is? I have an msdn case #: SRX050106605709 Thank you for any help anyone can give,
by: Lemune | last post by:
Hello. I need some help on building my sms services application (using modem). My application is to do some scheduling task based on daily, weekly and monthly. On the daily the choice are everyday, weekdays, or every what ever value of the days. On the daily the choice are every what ever value of the week on the selected days. On the monthly the choice are on what date, on what day of what week, and on selected month. My scheduling task...
by: Just_a_fan | last post by:
I tried to put an "on error" statement in a routine and got the message that I cannot user "on error" and a lamda or query expression in the same routine. Help does not list anything useful for explaining a "lamda" expression and so I don't know what one is and I am not doing any database stuff in the entire program. So what does this error message really mean and what can I do to get an On Error into the routine?
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.