• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • Last Modified:

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

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?
0
SchoolTechMichigan
Asked:
SchoolTechMichigan
  • 3
  • 3
1 Solution
 
reb73Commented:
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??
0
 
harfangCommented:
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

0
 
SchoolTechMichiganAuthor 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.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
harfangCommented:
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°)
0
 
SchoolTechMichiganAuthor Commented:
I think that tip is what I'm looking for harfang. I'm trying to get it working now.
0
 
SchoolTechMichiganAuthor 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.
0
 
harfangCommented:
Thank you and success with your project
(°v°)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now