Access Timer Timer Locking RRS feed

  • Question

  • Hi All,

    I have an access form (2013) that runs a timer event every 20 Sec and runs sql to insert (into) a few lines, only 7 lines, from the linked text file, which is continuously receiving data updates from another (non Access program), program, Metatrader 4.

    Metatrader 4 is updating data to the text file maybe 10 to 30 times per seconds.  It took me a month, but I found the reason that the timer locks, randomly, every two to five hours.  Using error trapping, I found that the Insert query was unable to run (message says it can't access the "access table name called data (insert into Data ....)".  Further testing shows that the link to the text file remains in tact so I think that the execution of the insert query is "bumping heads" with the Metatrader 4 update at the exact instant of the execution of the access append query.

    On Err_Handler, I added some "refresh Link" vba and a Pause function set to 5 seconds which is more that enough time to let Metatrader 4 finish its update of the text file.  The Error trap looks like this




    Resume Next

    These functions execute successfully but it doesn't seem to "resume next" and the form will sit for hours with the Time event NOT FUNCTIONING.

    So, here is my question:

    How can I keep the forms timer event running no matter what happens?  I need it to execute successfully at least every minute and, 97% of the time, it does perfectly.  How can I get that other 3%?

    Many thanks


    • Edited by Kody_Devl Wednesday, August 29, 2018 11:35 AM
    Wednesday, August 29, 2018 11:29 AM

All replies

  • Hi Kody,

    Here's maybe a potential workaround for you.

    1. Set either a static variable or a TempVar to indicate if an error occurred or not.

    2. When the timer fires, check the variable and reset it.

    3. If an error occurs during the insert, set the variable to indicate an error happened and then reset the timer to a shorter time.

    4. The next time the timer fires, reset the timer value.

    Hope it helps...

    Wednesday, August 29, 2018 3:16 PM
  • Hi theDBguy,

    Thanks for you thoughts.  What is the purpose / value for shortening the timer to a shorter time? 

    It is currently fires runs every 15 secs, though the code will only update (do the insert) ONLY once per minute. 

    So  the timer might fail but have 2 or 3 more shots at updating for the current minute. 

    Missing a minute is not as critical as getting home from work at 5 pm and find that the timer stopped updating at 10 AM.  Then I am missing processing for several hours (7 hours).



    Wednesday, August 29, 2018 4:00 PM
  • Hi Kody,

    To set the timer to fire at a shorter time, you can change the value of the Timer Interval property. For example, for the timer to fire every 15 secs, you probably have something like 15000 in there. So, if an error occurs, you can set the Timer Interval to say 5000 to make the form timer fire in the next 5 seconds. When it does and there's no error, reset it back to 15000 to back to normal operation.

    As for "knowing" or "avoiding" missing a process for several hours, you could maybe also add one more static variable to "count" how many times an error occurred. So, the first time an error occurred, set the counter to 1. If it errors again the next time it fires, increment the counter. If the timer/insert fires successfully, reset the counter back to 0. Then, either before you leave the office to go home, check the counter to see if there's been a lot of errors, or maybe set a certain upper limit and fire a message box (or even email) to let you know that you need to take care of an issue before you go home.

    Just my 2 cents...

    Wednesday, August 29, 2018 4:08 PM
  • Hi

    I understand.  I have a debug.print err.description in the error trap so it tells me how often the code runs in the

    error trap and

    After the 1. Refresh and 2. Pause5 (5 Sec)

    "Go To  TryToRunAgain"

    The code seems to just stop as reflected in the intermediate window debug.print messages.

    If it was working as I envisioned, the intermediate window should show continued Retry attempts until it succeeds.

    I was thinking about trying (never done before) putting a second error trap within the first error trap to see if there is an "error within and error" that is causing the timer to stop. 



    Wednesday, August 29, 2018 6:03 PM
  • Hi Kody,

    Just to clarify, your current approach is to try to perform the insert within the same event that started it in the first place. The approach I am suggesting is to let the current event finish running and attempt another insert on a brand new event that fires sooner than later (in case of an error).

    For example, let's say you do a debug.print of the current timer event along with an error message. So, if the process works for a while and then hits an error, your Immediate Window might look something like this:

    08:00:00 OK
    08:00:20 OK
    08:00:40 Error!

    The above example assumes two timer events fired and then the third one had an error. At this point, if I understand you correctly, the error message shows up, but the event is stuck in limbo at this point.

    With the approach I am suggesting, I am imagining the Immediate Window showing something more like this:

    08:00:00 OK
    08:00:20 OK
    08:00:40 Error!
    08:00:45 OK
    08:01:05 OK
    08:01:25 Error!
    08:01:30 Error!
    08:01:35 Error!
    08:01:40 OK
    08:02:00 OK

    In the above example, the same two initial events were ok and the third one errors out. However, the event fires again in the next 5 seconds and it was ok. Then, the event fires again after 20 seconds, still ok. The next event after 20 seconds errors out and continues to error out every 5 seconds until it was ok again.

    Hope it makes sense...

    Wednesday, August 29, 2018 6:19 PM