Results 1 to 10 of 10
  1. #1
    jrock1203 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26

    Auto refresh database

    Morning everyone!



    Apologies if this has been addressed already, I could not find it when I searched.

    Here's what I'm trying to do:

    1. I'm building a database for my job that will allow us to store QA data rather than using an endless assortment of excel files. I've build the tables, relationships, joins etc. and have imported all of the data.

    2. I'm building queries based on data points and trends we want to see.

    3. This db is shared amongst about 10-15 users, which is no problem, everyone can access it at the same time.

    4. I need the db to "auto refresh" every X amount of time.

    5. I changed the refresh interval in access options to 30 seconds, and it does not work.


    How do get the tables to auto-refresh so the queries are up to date, if you will.


    Thanks!!

    Using Access 2016 if that matters.


    Jeremy

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    This db is shared amongst about 10-15 users, which is no problem, everyone can access it at the same time.
    please clarify. Is the db split? does each user have their own copy of the front end?
    How do get the tables to auto-refresh
    refresh how? by loading new data from excel files?

  3. #3
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26
    Quote Originally Posted by Ajax View Post
    please clarify. Is the db split? does each user have their own copy of the front end?
    refresh how? by loading new data from excel files?

    No, not excel, sorry should've been more specific. If a user adds a new record to the table, let's say a batch number and a bunch of lab data, I'd like the table(s) to update on an interval so any other users would see the new data if perhaps they ran one of the queries.

    Does that make more sense?

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    it does, but I don't see why it matters, if user a adds a record, user b will see that record when they run a query. But you haven't answered my first question which may explain why things aren't working as you expected.

  5. #5
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26
    Ah! again sorry.

    DB is on a network drive and set to shared open mode and with edited record locking. Each user would be actively in the same file.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    that setup with multiple users will lead to corruption. It may already have started in some small ways, but at some point it will get to a point from which it cannot recover and you have lost everything, subject to restoring a backup (which itself will be corrupted, just not as much)

    1. The db needs to be split into frontend and backend. The backend contains tables only, the front end forms/reports/queries and code/macros. There are plenty of thread on this and other forums + ms where you can find how to do this
    2. once the db is split, the backend is located on your server and each user must have their own copy of the front end on their local machine, linking to the backend on the server. Again there are plenty of threads on how to do this and how to manage updating front ends on multiple machines.

    the refresh interval setting you are talking about refers to refreshing linked tables, so will have no effect in your current setup.

    As to why users are not seeing updates to tables when they run the query, they should see them so it is probably an early sign of corruption setting in. If you are talking about refreshing a query which is already open, you need to use a timer event on a form to refresh the query.

  7. #7
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26
    Quote Originally Posted by Ajax View Post
    that setup with multiple users will lead to corruption. It may already have started in some small ways, but at some point it will get to a point from which it cannot recover and you have lost everything, subject to restoring a backup (which itself will be corrupted, just not as much)

    1. The db needs to be split into frontend and backend. The backend contains tables only, the front end forms/reports/queries and code/macros. There are plenty of thread on this and other forums + ms where you can find how to do this
    2. once the db is split, the backend is located on your server and each user must have their own copy of the front end on their local machine, linking to the backend on the server. Again there are plenty of threads on how to do this and how to manage updating front ends on multiple machines.

    the refresh interval setting you are talking about refers to refreshing linked tables, so will have no effect in your current setup.

    As to why users are not seeing updates to tables when they run the query, they should see them so it is probably an early sign of corruption setting in. If you are talking about refreshing a query which is already open, you need to use a timer event on a form to refresh the query.

    So I've only built a draft version, and no one is using it yet. I had just finished reading up on splitting it. Really appreciate the suggestion, I'll get right on it.

    As far as the server is concerned, we don't have one - we have a NAS. Will that still work if it's is mapped correctly or do we literally need a server hosting the db?


    J

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Not heard of a NAS - what is it? a form of terminal server?

  9. #9
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    Quote Originally Posted by Ajax View Post
    Not heard of a NAS - what is it? a form of terminal server?
    A NAS stands for Network Attached Storage, its basically just hard drives that connect to a network and can be mapped to people's PCs.

    Quote Originally Posted by jrock1203 View Post
    As far as the server is concerned, we don't have one - we have a NAS. Will that still work if it's is mapped correctly or do we literally need a server hosting the db?
    Yes it will work using a NAS but it would make sense to map the NAS to everyone's PC using the same drive letter. That way when you send out the front end file to everyone you can have the linked tables set up to the NAS location where the back end is and each user won't have to relink the tables as the path is the same from every PC. Also the same when you send front end updates out to users.

    Does this make sense?

  10. #10
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26
    Quote Originally Posted by anthgav View Post
    A NAS stands for Network Attached Storage, its basically just hard drives that connect to a network and can be mapped to people's PCs.



    Yes it will work using a NAS but it would make sense to map the NAS to everyone's PC using the same drive letter. That way when you send out the front end file to everyone you can have the linked tables set up to the NAS location where the back end is and each user won't have to relink the tables as the path is the same from every PC. Also the same when you send front end updates out to users.

    Does this make sense?

    Excellent - and yes on the NAS. We will be setting up and actual server toward the end of the year, so that may help as well.

    I currently mapped every user's drive to the same letter, so good work on my part :-)

    The more I think about it, I totally agree and understand the split db. Solid suggestion all, I'll come back to thread if something is off.

    Really appreciate the input!


    Jeremy

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

Similar Threads

  1. RSS Feed Auto refresh
    By Ryan455 in forum Modules
    Replies: 2
    Last Post: 03-01-2018, 08:39 AM
  2. Refresh Database
    By Brightspark98 in forum Access
    Replies: 8
    Last Post: 01-17-2017, 04:01 AM
  3. Auto Refresh - ReApply Filter
    By jnoonan22 in forum Forms
    Replies: 6
    Last Post: 10-25-2014, 12:07 AM
  4. Replies: 7
    Last Post: 06-27-2013, 10:56 AM
  5. auto refresh the table content
    By mohammad8065 in forum Access
    Replies: 1
    Last Post: 02-27-2012, 01:54 PM

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