Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    KoMiKo is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    12

    Delay 1 -5 seconds when read from linked table

    I am testing 2 simple access applications on the same computer.
    The first application, after pressing the button, writes ActualTime = Now into its local table tbl.
    The second application uses this tbl table as a linked one. The form in the Form_Timer (100ms) reads the ActualTime entered by the first application, detects its change and calculate delay
    Code:
    Private Sub Form_Timer()
        rstData.Requery
        If Me.ActTime <> rstData!ActualTime Then
            Me.ReadAt = Now
            Me.ActTime = rstData![ActualTime]
            Me.Delay = Now - rstData![ActualTime]
        End If
    End Sub
    The problem is that the second application detects the change of the entered data only after a delay of 1 - 5 seconds from the time of its entry.



    Is there any possibility to detect the change of the data in the linked table immediately after its entry by other access app?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    not really clear on what you are trying to achieve but sounds like you need to reduce the refresh interval. See File>Options>Client Settings - advanced section

    Note that client settings only affect the the local instance of Access - i.e. it will affect all apps used by that machine - and if you forward a copy of the file to another user, the app will use the setting for that user and not the one you have set

  3. #3
    KoMiKo is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    12
    Thank you for your answer, but id did not help.
    My goal is as follows - when app no. 1 changes the data in tbl, app no. 2 should detect the change immediately (max 500ms) and not after a few seconds.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    what is rst and how long does it take to just requery that?

    debug.print now()
    rstData.Requery
    debug.print now()
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Micron's suggestion is a good one. It might also be good to know how the first app is saving the record. Maybe it's not being committed right away. You might also attach both files here so others can test and post results.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    KoMiKo is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    12
    Quote Originally Posted by Micron View Post
    what is rst and how long does it take to just requery that?

    debug.print now()
    rstData.Requery
    debug.print now()
    debug.print now() is not precise, it shows whole seconds. For precise time meassure I use "GetTickCount", it show precise time in ms :
    Code:
    t = GetTickCount
    rstData.Requery
    Debug.Print GetTickCount - t
    Result is 0 ms

    rstData is DAO recordset. But same delay I get when I read data from linked table with DLookup instead of DAO recordset.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    yes, then debug.print format(timer,"0.0000")
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    KoMiKo is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    12
    Quote Originally Posted by pbaldy View Post
    .... You might also attach both files here so others can test and post results.
    ReadLinkedtbl.accdb DB.accdb

    Save both files in C:\Temp to keep linked DB, or update linked table when open ReadLinkedtbl.accdb

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This may or may not help, but my testing duplicated yours. I had delays of 0-5 seconds. I have heard of something referred to as a "lazy write" with Access, where the data is not written immediately. I don't know anything about it though.

    On a lark, and because the test results annoyed me, I created a table duplicating yours on a SQL Server and linked both of your db's to it. The response is basically immediate. Most of my tests showed zero, a few showed 1 second. I think the 1 second test results are due to the 1 second resolution of the data type. I was seeing a result immediately, but it registered as a 1 second delay. In other words, the time was set at (for instance) 4:23:25.95 and read at 4:23:26.05.

    That would seem to confirm that the first db was not actually committing the record right away. That said, I don't know how to resolve it with an Access backend. Perhaps that gives you something to research though.
    Attached Thumbnails Attached Thumbnails 2022-10-20_16-26-15.jpg  
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    interesting result. couple of thoughts

    put the table in a separate BE and link both db's to it - mimicking Paul's setup with sql server
    perhaps use currentdb.execute rather than runSQL or maybe better assign currentdb to a db object and execute from there

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll be interested in that result. Forgot to mention I was using an Azure SQL Server, so it was immediate even with cloud latency.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    KoMiKo is offline Novice
    Windows 10 Access 2019
    Join Date
    Jan 2016
    Posts
    12
    Quote Originally Posted by CJ_London View Post
    interesting result. couple of thoughts

    put the table in a separate BE and link both db's to it - mimicking Paul's setup with sql server
    perhaps use currentdb.execute rather than runSQL or maybe better assign currentdb to a db object and execute from there
    I did more tests:
    - 1 BackEnd + FrontEnd Write + FrontEnd Read
    - used CurrentDb.Execute to write
    Code:
    CurrentDb.Execute "UPDATE tbl SET ActualTime = '" & Now & "'  WHERE ID = 1;"
    - used DAO recordset to write
    Code:
    ... 
      rstTbl.Edit
      rstTbl![ActualTime] = Now
      rstTbl.Update
    ...
    - used direct "manual" write to table "tbl"

    ...no difference, always delay 1 to 5 sec

    Looks like there is no way to eliminate the delay with an Access backend.

    Thank you for your effort.

  13. #13
    KoMiKo is offline Novice
    Windows 10 Access 2019
    Join Date
    Jan 2016
    Posts
    12
    The real problem I am solving in my app is the following:
    • One BE database
    • 1 Master FE application on one PC
    • 1 or more Slave FE applications on separate PCs in a common LAN.

    The user operating the Master FE application presses the Start button and this action must immediately, without delay, start the timers on the Slave app.

    Due to the Delay problem, I now use the following method for starting the timer:
    • Master FE writes a TXT file with the current time to the directory where BE is
    • when the Slave FE detects that the file has been written, it starts a timer.

    It works instantly, no lag, but it feels like a stone age method to me .

    Do you know of any other method for instant communication between Access FEs on a LAN that could be used?

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    When I was poking around yesterday I saw something about closing the DAO recordset after saving the value, so try that.

    rstTbl.Close

    It's a shot in the dark, but who knows. Good job finding a workaround though!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    KoMiKo is offline Novice
    Windows 10 Access 2019
    Join Date
    Jan 2016
    Posts
    12
    In my previous post i published only fragment of DAO recordset code what I tried today morning. Full code is here:
    Code:
           Dim strSql As String
           Dim rstTbl As DAO.Recordset
           strSql = "Select * FROM tbl WHERE ID = 1"
           Set rstTbl = CurrentDb.OpenRecordset(strSql)
           rstTbl.Edit
           rstTbl![ActualTime] = Now
           rstTbl.Update
           rstTbl.Close
    As you can see, rstTbl.Close was already here and it didn't do the job.

    Good job finding a workaround though!
    Yes workaround with TXT file works, but I'm not very proud of this solution...

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to create linked tables only read only?
    By jaryszek in forum Forms
    Replies: 5
    Last Post: 07-13-2018, 07:57 AM
  2. Linked Table All of a sudden changed to Read Only
    By RayMilhon in forum SQL Server
    Replies: 2
    Last Post: 11-11-2016, 04:02 PM
  3. Replies: 9
    Last Post: 10-20-2014, 04:00 PM
  4. Replies: 7
    Last Post: 07-31-2014, 07:22 AM
  5. Create Linked Read-Only File
    By kazaccess in forum Access
    Replies: 10
    Last Post: 02-13-2014, 08:43 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums