Automatically Run an access query at a specified time

Posted on 2006-05-26
Last Modified: 2012-06-27
Is there a way to set a query or VBA module to run automatically at a certain time each day?
Question by:jrimmele
    LVL 65

    Expert Comment

    u could use the windows task scheduler to run a VBa macro

    u can pass in parameters like /x macroname in access

    alternatively u can create a macro called AutoExec
    this will run your code/query then exit the DB
    LVL 65

    Expert Comment

    here are the switches for msacess

    as you can see u pass in

    /x macroname into msaccess.exe (note full path of msaccess.exe will have to be specified)

    LVL 27

    Expert Comment

    Hi jrimmele,

    you will have to have a Form that is open at the time when you want this to happen,
    I usually have the main Form always open, and you can make the Time interval of the Form to 1 minute (which is 1000 x 60),
    and have the following code "On Timer" of the Form

    Private Sub Form_Timer()
        Dim TheTime As Date
        TheTime = Format(Now(), "hh:mm")

        Select Case TheTime
            Case #10:30:00 PM#
                DoCmd.openquery "your query name"
        Case Else
                'do nothing
        End Select

    End Sub


    Author Comment

    I was able to get it to work through the task scheduler.  What I was really hoping for was to be able to do was to run the process in the middle of the night sometime, but it doesn't seem to work unless the PC is on - is there a way to get the task to run even if the computer is not on?
    LVL 65

    Expert Comment

    urm, no.
    if the computer is on, then no programs run

    You have to leave it on. I guess what u could add is some code that shuts down the PC after it runs your job
    here is an example

    LVL 65

    Expert Comment

    urm, typo

    if the computer is NOT on, then no programs run

    LVL 65

    Accepted Solution

    ok, found this. Perhaps this may help

    u can use windows task scheduler to start pc, but that pc has to be in hibernate/standby mode,aid,124169,00.asp

    so one task to start pc
    one to run app - which when finishes running, shutsdown pc

    if u do all this, I suggest u add logging so u know whether it worked or not

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now