Results 1 to 13 of 13
  1. #1
    JayRab is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2013
    Posts
    78

    ODBC Linked Table Data Not Refreshing

    Have an access database front end that is pulling from a SQL backend setup as a Machine Data Source read only.

    I have forms and reports getting information from the linked tables. I cannot get these tables to refresh their data. Only 161 rows in this specific table and only a few of the columns are changed as data is input from the main system.

    I have tried using the linked table manager to no avail.

    If I close and immediately reopen the database the tables are updated with the latest data?



    Any help as to why I cannot update the ODBC connected tables.

    I have lowered the ODBC refresh rate to 5 mins and I have waited or checked (i am working on other forms and reports with the linked tables) for 3 hours with no update of the data until I close and reopen the database as a whole...

    Thanks for any help.
    JR

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    SQL backend setup as a Machine Data Source read only.
    I cannot get these tables to refresh their data
    If I close and immediately reopen the database the tables are updated with the latest data
    The above quotes explain why the tables can't be refreshed

    As I read it, the backend is read only so cannot be updated whilst it is in use
    When you reopen it, the latest data is read in and will then itself be read only

    So remove the read only restriction & it should always show the latest data
    If you don't want users to edit the data, lock the forms.
    Users should have course have no access to the tables!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    JayRab is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    78
    Opening with same log same connection as read only in MySQL the tables update no problem?

    IT will not release full log in so stuck with read only at this time.

  4. #4
    JayRab is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    78
    to update my access front end is making no changes to the sql data just reading it and joining it with other data.

    Using QlikView I can also pull the data and reload the script to get the data to update. All research I am seeing is dealing with actually editing the sql back end data, which is not something I am looking to do.

    Anyone else have any ideas?

    The data is machine data which includes current state, idle time, up time, down time, and other OEE type data gathering for the current shift of 161 assets. Its refresh rate is less than 1 second. I am looking for maybe 5 mins or at the very least being able to update with a button click.

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I am wondering how you are determining that it is not refreshing. If you have a form or report or query open then it will not change. Only by refreshing what you are looking at will the data change, otherwise it will stay the same. Closing and reopening, or doing a Requery.

  6. #6
    JayRab is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    78
    I have buttons that apply sort and filter operations that calls for requery. I have also closed all forms all reports and reopen them. While the reports were closed I used the linked table manager to do what i thought was reload the data.

    I can view the data at its source and can see it change. I can open my report database and immediately close and reopen at ANY time interval and it will update but only if I close and reopen the database.

    I have also had qlikview, mysql, and access open at same time with same log in credentials on all and refresh all and only access didn't refresh the data until I closed and reopened the database.

  7. #7
    JayRab is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    78
    Switched the ODBC connection from a machine data source to a file data source and all seems to be working.

  8. #8
    JayRab is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    78
    Well I did have it working but as soon as I save close and reopen the connections no longer update.

    When i initially setup the connections I can get them to update at any interval, but once I save the database close and reopen I cannot get data to refresh unless I close and reopen?

  9. #9
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    From memory there is a refresh property in the ODBC connection properties, what was it set to?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    JayRab is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    78
    I am using ODBC 5.1 32bit and it is working correctly for now as the big issue was the network and I expanded the amount of data I was trying to pull, 1mil+ records etc....

    I will create a work around possible serval databases the keep only the data I need for trend analysis.

    Just need to work on getting the updates consistently completed without me needing to apply filters to the raw table.

    Of course manually sorting, apply filters, or refreshing the linked tables updates the table data and the subsequent refresh of the open forms and attached query. Will need to accomplish this programmatically, Me.Fresh and Me.Requery does not update grouped sum-ed query and form.

    Any tips?

  11. #11
    JayRab is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    78

    Anything Better?

    Well I have worked to try and figure out how to easily get this data to update and the following code is the only way I can get it to work is there a better way to do this?
    Code:
    DoCmd.OpenTable "ss_cur_base", acViewNormal, acReadOnlyDoCmd.ApplyFilter , "mach_seq = 10477"
    DoCmd.RunCommand acCmdRemoveFilterSort
    DoCmd.ApplyFilter , "mach_seq = 10477"
    DoCmd.RunCommand acCmdRemoveFilterSort
    DoCmd.Close acTable, "ss_cur_base", acSaveNo
    
    
    Me.Requery
    Me.Refresh
    I am using this code on a form timer as well as refresh buttons throughout the different forms and dashboards.


    If there is another better way to do this let me know.

    Thanks for you time
    JR

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by JayRab View Post
    I am using ODBC 5.1 32bit and it is working correctly for now as the big issue was the network and I expanded the amount of data I was trying to pull, 1mil+ records etc....
    "ODBC 5.1" is really old. Not even an option on my computers. (Win7 and 8.1)
    I'm trying to connect a Win 8.1 computer to SQLSE 2014 and had to add ODBC drivers. The latest ODBC driver appears to be ODBC 13.

    My Win 7 computer has "SQL Server", "SQL Server native Client 11.0" and "ODBC Driver 11 for SQL Server.
    Name
    Driver Name
    Ver Date
    ODBC Driver 11 for SQL Server MSODBCSQL11.DLL 2014.120.5000.00 6/18/2016
    SQL Server SQLSRV32.DLL 6.01.7601.17514 11/20/2010
    SQL Server Native Client 11.0 sqlncli11.dll 2011.110.5643.03 11/21/2015
    I 'm going to try adding "ODBC Driver 13.0"


    You might look at adding a newer ODBC driver.....

  13. #13
    JayRab is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    78
    The software that is creating the information is pretty dated as well. I will contact them to see if I can use an updated driver to access the data.


    We do not have any issues with the 5.1 on our win7 and win10 devices.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  2. Replies: 2
    Last Post: 04-30-2015, 08:40 PM
  3. Replies: 2
    Last Post: 04-30-2015, 10:38 AM
  4. linked in table need refreshing in backend?
    By vientito in forum Programming
    Replies: 3
    Last Post: 10-16-2014, 08:33 AM
  5. Replies: 8
    Last Post: 09-27-2012, 08:23 AM

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