Results 1 to 7 of 7
  1. #1
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43

    Remote/VPN users having issues with Access 2010

    Hello, all. Not quite sure if this is the proper category for the question, but here goes...

    Long story short, we have ~10 to 12 users who are working from home and connect to the local network through a VPN (SonicWall router as the endpoint and the SonicWall VPN client on the remote users' computers). Our WAN connection is roughly 10Mbps/2Mbps. There are maybe 3 users with full versions of Access while everyone else has the Access 2010 Runtime installed.

    What's happening is when they try to open the form(s), it's really slow. Like 8 - 20 seconds just to open, whereas on the local network it's around 1 to 5 seconds depending on the form (the forms do need a good deal of optimization, I know). They also occasionally run into an error where Access tells them it needs to rebuild the database and create a backup which takes time and causes some concern.

    Our transition to Access is very new and the rollout has only been live for roughly a week, so I don't have a lot of specific feedback from the users, only the reports on the slow speed and the error->rebuild events. The slow speed really came as no surprise due to the slowdown that comes with a VPN. I don't know enough about SonicWall as a solution or if it's being pushed beyond what it's really capable of. I think it's also possible that the connection speed is playing a part. It may also be a matter of patience. Like, people thinking Access has crashed when it's really just slow to load, so kill the app before it's done doing what it was trying to do.

    Any thoughts? Since I don't know how long we will be having these folks work from home due to the COVID-19 thing (could be 3 months, could be 6 months, I just don't know) I am reluctant to buy a better/more apt VPN appliance. Same goes for upping the connection speed. Do you think running a newer Access runtime would help? Say, Access 2019 Runtime?



    At this point any suggestions would help.

    Thank you!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Is everyone running their own copy of the FrontEnd?

    (they should NOT be running the same NON-split db)

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    this is a common question these days. VPN is slow, better to use terminal server - performance for the user will be similar to them having the back end on their local machine. But it comes at a cost, so you need to decide whether it is worth it now and in the longer term.

    You do need to optimise your forms - see post #5 in this link about pretty much the same question

    https://www.accessforums.net/showthr...erminal+server

    Do you think running a newer Access runtime would help? Say, Access 2019 Runtime?
    not in the slightest

  4. #4
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43
    ranman256,

    I must apologize I'm not quite sure what non-split is. My role is/was more VBA, form/query interactions and such. I work with our QC guy on this and he was more in charge of database/information layout.

    That being said, our current structure is like this:
    • Users have a local shortcut that points to the database in question (on a local network server). The database is set to open a splash screen/menu with buttons to open various forms within that database.
    • The database does have native as well as linked tables (no copies) housed in other local databases. There are 3 databases total, essentially one for each department and each department has info that the others may need.
    • Forms have the usual objects, both bound and unbound, some with (in my opinion) ridiculously long domain functions and nested iifs (one of the reasons for the wait times, but this is after the form loads)


    When I get a minute I will look into the splitting of front ends and make sure we didn't go too far off the reservation. Thank you for the direction!



    Ajax,

    I'll definitely look into terminal server. I really wish I had a better idea as to how long this whole pandemic thing is going to keep us in quarantine mode. I wouldn't mind stumbling along for another month or three, but longer than that I would need to make this system at least manageable/tolerable. If it were just one or two people remoting in I'd just set them up with AnyDesk or Teamviewer to their computer here at the office. I really do thank you for the advice!


    Thanks again, all!

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Citrix might be another option if you can afford it. Regardless, if the home users are connected via wifi that will not only slow things down but I'd worry about them corrupting the data. In a split db environment, it's not likely they'd corrupt their own front end, but you might corrupt tables if records manage to be partially created and there is missing required data (required by table design, not by code). IIRC, a Citrix server will allow a session to be re-established if it gets dropped, which aids in protecting the db from an unrecoverable situation.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    To be clear, access should be split into two parts. A backend which only contains tables and is located on a server. And a front end which contains everything else - forms, reports, queries, code and macros and a copy of which is located on each users local machine. The front end can be connected to the back end in a number of different ways but typically by using linked tables.

    As others have said if a connection is interrupted you risk corrupting the back end - so at least make sure you take frequent backups of the back end.

    A potential low cost option to reduce the risk of corruption (but not improve performance) is to transfer your access backend into sql server express (which is free) - the cost comes in undertaking that exercise and modifying your front end (which may just be a matter of linking to sql server express tables instead of access ones).

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If your life is not stressful enough, I wanted to reiterate:

    if you do not have your dB split into a FE/BE (and the BE is Access)
    and each user does not have a copy of the FE on their local computer
    and/or your employees are connecting by WiFi,

    it is not a matter of IF the dB gets corrupted, it is just a matter of WHEN.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-18-2016, 09:06 AM
  2. Replies: 1
    Last Post: 10-16-2016, 10:15 AM
  3. Email picture not showing for remote users
    By trevor40 in forum Programming
    Replies: 2
    Last Post: 08-12-2016, 01:02 AM
  4. ADP Project Issues with Access 2010
    By johnson8809 in forum Access
    Replies: 2
    Last Post: 12-15-2015, 06:22 AM
  5. disconnected Remote Users?
    By Zermoth in forum Programming
    Replies: 0
    Last Post: 12-11-2008, 04:52 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