none
MS Access Application runs slow over wireless RRS feed

  • Question

  • We have problem with our MS Access Application that runs over wireless.

    we have application front end (access forms) on the desktop and access database on the server. There is table linking from access on the desktop with the access database on the server.

    The application runs very well over the LAN with 10-15 concurrent users and it behaves extremely poor when it runs over the wireless.

    we would like to know what could cause this issue? or whether this is a current known issue on access over wireless? if so what is the resolution.

     

    Many thanks in advance,

    Thursday, May 5, 2011 5:53 AM

Answers

  • Hi,

    it's a really well-known issue and we can even say that it's by design. Originally Access isn't designed for WAN operating. In my practice I've been able to set up my application for 100 users in WAN segments. There was acceptable performance but not perfect. There are a lot of techniques, such as:

    1. Always pull as little number of data as possible (use appropriates filters, WHERE clauses).

    2. If you should load a lot of data, try to separate this process logically. For example, if you have a Tab Control, load only data for the first page when your form is opening, and then load other data for each page when this page is being viewved by a user (Change event of a Tab Control).

    3. For a bunch of action queries use Transactions and DBEngine(0)(0).Execute.

    4. You can consider to use some local tables for your combo/listboxes Control Source.

    In the other words, there is no such an one-click-option to increase a 'WAN performance'. If you are able to do this, then consider to move your back-end to SQL Server. I gained an extreme perfomance improvement this way. At least, you can use Express edition which is free.

    Some other links you may find interesting:

    http://kallal.ca/Wan/Wans.html

    http://social.msdn.microsoft.com/Forums/en-IE/accessdev/thread/cfe4b983-cd7e-404e-875e-bc3421a9e3ee


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Bruce Song Monday, May 16, 2011 7:59 AM
    Thursday, May 5, 2011 6:08 AM
  • You have some great advice here.

    One  good question would be to ask how much slower is the wireless system? I mean, is it 10 or 100 times slower than your wired network? If it is 10 times (most likely), then something that takes 4 seconds will now take 40. So, I would ask and find out how much slower the wireless network is (and do not get an answer like some such mega bits and mega bytes, but a simple answer of it is 5 times slower than the hard lines network).

    As noted, as a general rule such a network will not work well with Access due to wireless often breaking the connection. I explain this "break" issue here:

    http://www.kallal.ca//Wan/Wans.html

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canad

    • Marked as answer by Bruce Song Monday, May 16, 2011 7:59 AM
    Thursday, May 5, 2011 4:07 PM
  • On Thu, 5 May 2011 06:08:25 +0000, Andrey Artemyev wrote:
     
    > If you are able to do this, then consider to move your back-end to SQL Server. I gained an extreme perfomance improvement this way. At least, you can use Express edition which is free.
     
    We successfully run Access - SQL Server applications in horrendous
    environments all the time - wireless, cellular data, VPN, DSL, T1...
    sometimes all at the same time.  :)  I've even demonstrated that they
    work, a bit slowly perhaps, over an old fashioned modem connection.
    You just need to use proper client-server coding principles.
     
    If you do decide to move to a SQL Server back-end, I've written a
    PowerPoint presentation on techniques for using Access as a
    client-server front-end to SQL Server databases.  It's called "Best of
    Both Worlds" at our free J Street Downloads page:
     
    It includes some thoughts on when to use SQL Server, performance and
    security considerations, concurrency approaches, and techniques to
    help everything run smoothly.
     
    Cheers,
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    • Marked as answer by Bruce Song Monday, May 16, 2011 7:59 AM
    Friday, May 6, 2011 3:26 AM

All replies

  • Hi,

    it's a really well-known issue and we can even say that it's by design. Originally Access isn't designed for WAN operating. In my practice I've been able to set up my application for 100 users in WAN segments. There was acceptable performance but not perfect. There are a lot of techniques, such as:

    1. Always pull as little number of data as possible (use appropriates filters, WHERE clauses).

    2. If you should load a lot of data, try to separate this process logically. For example, if you have a Tab Control, load only data for the first page when your form is opening, and then load other data for each page when this page is being viewved by a user (Change event of a Tab Control).

    3. For a bunch of action queries use Transactions and DBEngine(0)(0).Execute.

    4. You can consider to use some local tables for your combo/listboxes Control Source.

    In the other words, there is no such an one-click-option to increase a 'WAN performance'. If you are able to do this, then consider to move your back-end to SQL Server. I gained an extreme perfomance improvement this way. At least, you can use Express edition which is free.

    Some other links you may find interesting:

    http://kallal.ca/Wan/Wans.html

    http://social.msdn.microsoft.com/Forums/en-IE/accessdev/thread/cfe4b983-cd7e-404e-875e-bc3421a9e3ee


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Bruce Song Monday, May 16, 2011 7:59 AM
    Thursday, May 5, 2011 6:08 AM
  • Also,

    I've just thought that WAN is not always equal to Wireless. Wireless is even worse since it is unstable and the risk of a database corruption significantly increases. So, it is highly recommended to move your back-end to a server.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Thursday, May 5, 2011 6:18 AM
  • You have some great advice here.

    One  good question would be to ask how much slower is the wireless system? I mean, is it 10 or 100 times slower than your wired network? If it is 10 times (most likely), then something that takes 4 seconds will now take 40. So, I would ask and find out how much slower the wireless network is (and do not get an answer like some such mega bits and mega bytes, but a simple answer of it is 5 times slower than the hard lines network).

    As noted, as a general rule such a network will not work well with Access due to wireless often breaking the connection. I explain this "break" issue here:

    http://www.kallal.ca//Wan/Wans.html

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canad

    • Marked as answer by Bruce Song Monday, May 16, 2011 7:59 AM
    Thursday, May 5, 2011 4:07 PM
  • On Thu, 5 May 2011 06:08:25 +0000, Andrey Artemyev wrote:
     
    > If you are able to do this, then consider to move your back-end to SQL Server. I gained an extreme perfomance improvement this way. At least, you can use Express edition which is free.
     
    We successfully run Access - SQL Server applications in horrendous
    environments all the time - wireless, cellular data, VPN, DSL, T1...
    sometimes all at the same time.  :)  I've even demonstrated that they
    work, a bit slowly perhaps, over an old fashioned modem connection.
    You just need to use proper client-server coding principles.
     
    If you do decide to move to a SQL Server back-end, I've written a
    PowerPoint presentation on techniques for using Access as a
    client-server front-end to SQL Server databases.  It's called "Best of
    Both Worlds" at our free J Street Downloads page:
     
    It includes some thoughts on when to use SQL Server, performance and
    security considerations, concurrency approaches, and techniques to
    help everything run smoothly.
     
    Cheers,
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    • Marked as answer by Bruce Song Monday, May 16, 2011 7:59 AM
    Friday, May 6, 2011 3:26 AM
  • Hi Aschool,

    Have you resolved your problem yet, and do the suggestions help you? If you still have any concern on the thread, feel free to follow up.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 12, 2011 3:33 AM