Results 1 to 2 of 2
  1. #1
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56

    VPN or MySQL what should we do.

    We have an access database running on our servers at the office. Now we have a situation where two people will be working from home or abroad for about 6 months. My question is this. Since Sharepoint seems to have been a bust, should I go with some VPN or should I migrate to a SQL backend. Im not totally unfamiliar with SQL but if we go SQL will the access forms we use work with any of the backend data? Or do i have to redo every freaking thing we have done in the last few years?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    but if we go SQL will the access forms we use work with any of the backend data?
    in principle yes. Depends on how good your design is. Performance may be hit if your existing queries are not written efficiently and/or use non sql server functionality such as domain functions, udf's etc. Everything being equal, access and sql server have much the same performance but sql server does have a few more ways to speed things along for more complex requirements which would require modifications to your queries.

    Forms should be limited to return just one record or short list, not the whole table which is subsequently filtered, etc. This also applies to subforms linked to main form through the link child/master properties. Note that using the WHERE parameter for docmd.openform applies a filter, not a criteria.

    Keep lists short, don't display the whole table for the user to filter, instead display nothing (or perhaps just a couple of records) until the user has completed at least one filter - then go fetch the data - if searching for a name - even the first letter is better than nothing. Simplistically applying the first letter will reduce 100k records to 4k records. And avoid using the initial * with Like, that prevents the use of indexing.

    The objective is to limit the amount of data that is transmitted across the network - this applies to both to access and sql server.

    You might be better considering using terminal server - performance will probably be better than you get at the moment (it would be like each user having the backend on their machine) and the only changes required would be the linking of tables. Both front end and back end would be on the server, each user has their own profile, so they still keep their own copy of the front end. Note all users would have to go onto terminal server.

    If you want to go the sql server route, consider using sql azure.

    You also need to tke into account if your remote users need access to other stuff such as excel/word/powerpoint files etc. That might have a bearing on which route to take

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

Similar Threads

  1. MS SQL or MySQL?
    By Historypaul in forum SQL Server
    Replies: 6
    Last Post: 12-14-2014, 01:00 PM
  2. Access to SQL or MySql
    By Randy in forum Access
    Replies: 6
    Last Post: 08-05-2011, 08:38 AM
  3. mysql connecting help....
    By alex_raju in forum Access
    Replies: 1
    Last Post: 07-18-2011, 07:22 PM
  4. MySQL & Access
    By Scorpio11 in forum Database Design
    Replies: 3
    Last Post: 05-21-2011, 02:32 PM
  5. Using DSN alternatives with MYSQL
    By ajetrumpet in forum Access
    Replies: 2
    Last Post: 10-14-2010, 12:55 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