How do I set an Access database query to refresh/requery more frequently.

Posted on 2009-02-17
Last Modified: 2013-11-27
I normally work as desktop support and I don't have a strong programming background, please be forgiving with me.

I need to have several people accessing different queries in an access database. There is a certain query for each of them. I need those queries to be updated frequently, probably about every 30-60 seconds for about 45 minutes. Can anyone suggest the best method for this? Is there a simple timer that could be set?
Question by:SchoolTechMichigan
    LVL 25

    Expert Comment

    Queries are dynamic by nature, they work only when executed.. They don't have static data like tables..
    What do you mean by 'queries to be updated frequently'? Is it the definition of a query that needs to be updated??
    LVL 58

    Expert Comment

    If you display the query result in a form, you could use the form's timer event to force a refresh of the query at regular intervals. For example, let's assume the query is displayed in the sub-form control "subDetails", and you have set the form's timer interval to 30000 milliseconds.

    Private Sub Form_Timer()
    End Sub

    Open in new window


    Author Comment

    Apologies. I believe my title was rushed.

    It does not need to be more frequently as it does not refresh at all without hitting the F5 key or clicking refresh. More correctly, it needs to refresh independant of user interaction.

    Here's my task, which is a bit out of my league. I need a system to notify teachers when a student's ride has arrived at the end of the day.

    The parameters I was given: Cars/Families/Parents would be given an ID number. They would provide that number when they arrive at school for pickup. That ID would be associated with all students who ride together.

    The time intensive solution would be just to have a searchable list or filtered database and have secretaries call the teachers or email them. This would run too slowly I believe as parents would be arriving about a dozen per minute.

    My plan was to have a database that would have queries for each teacher displaying the names of the students that would be picked up.

    I've made it this far:
    1. A table with the Student Name, Teacher, and Car Pool ID.
    2. A form for entering the Carpool ID into a new table.
    3. A query that checks the carpool ID against the list of students to provide a list of which students have rides waiting for pickup (queryPickupList).
    4. A query for each of the teachers that will show only results from their class from queryPickupList by checking against the Teacher field.

    It works fine except that as the secretaries into the Carpool IDs indicating new rides, the queries do not update unless the teachers frequently hit Refresh. I want to avoid the level of teacher interaction required here. I am searching for some way to automate the refreshing of the queries, maybe a timer of sorts or some way to check against the original tables and have the queries refresh as the tables get updated.

    Setting up the manual part of the database was within my comfort zone. Queries, macros, and scripts is somewhat outside my comfort zone but I am trying to learn.
    LVL 58

    Accepted Solution

    You could use the following setup:

    Each teacher opens the database, identifies him/herself, and keeps a form open. That form displays the list of students with their pickup information (arrived or not). The form's timer event will fire at the interval you set (for example every few seconds) and basically "hit refresh" so that the teacher won't have to.

    The skills needed are really limited: one single line of code, or a very simple macro tied to the form's timer event.

    The first step would be to embed the query into a form: either directly in a subform control or by creating a form based on the query. Can you do that?


    Author Comment

    I think that tip is what I'm looking for harfang. I'm trying to get it working now.

    Author Closing Comment

    Sorry about the delay with responding. That worked well. I took your suggestions and imbedded the queries as a subform into a form for each teacher. I used the timer event to schedule a requery macro every 30 seconds. I had some trouble as I wasn't sure where to find the event timer option, but I dug it up. Thanks for all the help.
    LVL 58

    Expert Comment

    Thank you and success with your project

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now