We help IT Professionals succeed at work.

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

Medium Priority
1,668 Views
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?
Comment
Watch Question

Commented:
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??

Commented:
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.

(°v°)
Private Sub Form_Timer()
    Me.subDetails.Requery
End Sub

Open in new window

Author

Commented:
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.
Commented:
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?

(°v°)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

Author

Commented:
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.

Commented:
Thank you and success with your project
(°v°)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.