Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    louise is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    112

    Advice on moving Access to the cloud


    My non-profit organization has subscribed to MS Office 365 for Business and will have Azure SQL server available. We are planning to move our two Access databases to the cloud, in order that others can generate reports, a few with permission can add/edit data, and someone can step in for me (currently the creator and only user).

    I am seeking some advice on what I understand to be two options:
    - to put the backend on the Azure SQL server and the front-end on the computers of a few users (at the moment we have enough licenses to do this, but it is inconvenient to uninstall and install as board members, volunteers change) and I am afraid that it isn't a good long-run solution. Is this a solid option? Short-sighted? (And, if it is advisable, are there articles and lists of precisely what I should to to get the dbs ready for the migration?)

    - to put the databases entirely on the web so we aren't limited to licenses and who the users are. Is this an entirely separate thing from Access or are there conversion options? Is this a huge overkill for a small all-volunteer organization? And a very steep learning curve? Would this allow links to our other Quickbooks and ArcGIS databases?

    I am worried about getting in way over my head on this (we have a technical MS consultant, but I am the only person in the organization to do this work) and, other hand, don't want to make a too-short-run decision.

    Any thought and advice would be appreciated!

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can use an Access front end with a Free Runtime version of Access, no licence required.

    Access works well with Azure provided you design/adjust things to restrict the number of records being pulled over the network.
    Once your data is in Azure you can get a web based interface to your data as well or instead of, but bear in mind that the development costs will be 5-10 times whatever time it would have taken you to create the equivalent Access front end.
    Also any tweaks or changes will incur more development costs.

    Access is a RAD tool, and is very useful because of it.
    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 ↓↓

  3. #3
    louise is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    112
    Thank you very much! I will check out the Free Runtime version of Access.

    Can you refer me to any lists or articles about what I should do to my dbs before I move the backend to Azure? I have seen a few things - e.g., primary keys and name formats -- but have not found a systematic approach to the topic.

    And, relatedly, things I should know about getting things ready to split the data base.

    Thanks again.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    An alternative that you haven't mentioned would be have the Azure BE with Access FEs for the main users and for the changing volunteers to have them access it via a remote server solution (Citrix or MS), that way you only need one license for the server.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    louise is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    112
    Thank you , Gicu,

    Excuse this beginner's question: In this suggestion, where Azure has the whole of the database, front- and back- ends, would I (as the main user) then work directly through the internet connection and not have Access software on my computer at all?

    And, is the "remote server solution" for the changing volunteers the same as the Free Runtime version of Access that Minty mentioned?

    Thanks!

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Louise,
    No Azure would still only be hosting the back-end only. You would still have Access as the main developer, and maybe the more permanent of the staff, but the remote desktop option allow you to allow access to the application to multiple users without having to purchase multiple licenses for each one. The runtime version that Minty mentioned would work in most cases but it does have some limitations, so the remote option would allow one full license to serve multiple users (as they will not have Access on their machines but would run it on the remote server).
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @gicu. Not sure I agree with your suggestion one license many users on Remote Desktop, unless things have changed my understanding is each user will have their own profile so needs their own copy of access - which could be the free runtime

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by Ajax View Post
    @gicu. Not sure I agree with your suggestion one license many users on Remote Desktop, unless things have changed my understanding is each user will have their own profile so needs their own copy of access - which could be the free runtime
    I agree with this for multiple users. And you would need a RDP seat licence for each user form what I remember, unless the licencing arrangements have changed.
    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 ↓↓

  9. #9
    keviny04 is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2015
    Posts
    128
    I just did a similar thing: moving my SQL Server database to the cloud and having my Access front-end connect to it. Before the move, all data were accessed locally and my Access front-end was super-fast as expected. After the move, all data are now accessed remotely and my Access front-end is slow to a crawl. Access is simply not made for client-server environment, that's really the reason. When you designed your front-end, you likely didn't have client-server in mind (neither did I with mine). I suggest you find a way to test how slow your current front-end would be in your new environment, then plan the right course of action. If your front-end is too slow, you're gonna have to re-design your Access front-end specifically for client-server. Or you give up Access front-ends for something else that is inherently made for client-server, such as a web interface. Access is a product of the 90s that is unfit for today's cloud computing usage. Almost everything that Access does consumes lots of data, which makes it unfit for Internet. Internet speed would have to be hundreds of times faster to be comparable to local speed. Simply opening a table in Access consumes a lot of data: I get a ONE SECOND DELAY every time I scroll down a table full of remote data, whereas it is instantaneous for local data. In my home and business, I use Access only for local data, and use web interfaces for remote data. You can use Access for remote data too if you want, but as I said, you likely have to re-design everything for it. If you need to start from scratch, you may as well do it in an environment that is inherently made for client-server.

  10. #10
    keviny04 is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2015
    Posts
    128
    Just want to add to my previous post: one key factor in such a migration is how to do your queries. Access does its native queries locally, i.e. a query pulls data in and process them. That slows things down if the data have to be pulled in remotely. But if a query is made directly on SQL Server, it runs REMOTELY on the remote server -- no data are pulled in until the query returns the output. This consumes much less data, befitting cloud computing. In other words, Access queries are run "client-side," whereas SQL Server queries are run "server-side." To have speedy performance with a remote server, you want everything to run at the server side as much as possible, and that's exactly what Access can't do natively. To "make" Access run things server-side, you have to rewrite all Access queries into SQL Server queries that run server-side, which have different SQL syntax from Access queries -- enough of a difference to lead to often time-consuming rewrite of queries. I did something similar years ago and I had to rewrite and debug many lengthy queries, and had to rewrite all VBA code that used queries too, which took me many hours.

    The above method only works if your queries return very few data. If not, nothing can help until and unless you redesign the queries so that they return very few data. For instance, if you have a bound form in Access that has an underlying query that pulls in lots of data for convenient browsing of records, you are in effect consuming lots of data, which is a no-no in a client-server environment. You usually have to change the forms to unbound forms that only display data the users need to see (thus minimizing data usage), which would require all new VBA code.

    I sound like an alarmist, but all I've said are true, and all other Access experts here can attest to that. If your Access tables only have 5 to 10 records then maybe you can get by without doing anything. But if you have hundreds, thousands, hundreds of thousands, etc. records, you have to do significant re-design of how data are retrieved (the foundation of every database) in your application.

  11. #11
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Some of what @kevinoy04 is said above is broadly correct, but a majority of the slowness is caused by poor design e.g. loading forms with every record available.
    Bound forms still perform very adequately if properly designed, and many developers use SQL and Azure as a back-end without any issues, as they design with that in mind from the get go.
    Pulling in every record even with an Access backend is very inefficient.

    With respect to the queries he is wrong - ODBC will make a majority of queries run very efficiently as it knows how to provide the correct syntax to SQL Server(you can prove this by looking at the execution plans and data exchanged between the two), provided they don't include any local VBA functions.
    If they have any non native functions in them then all the records need to be pulled into Access to be processed, so if you use these they will need a rethink or moving to the BE.

    Where required the power of a server based query or view will more than overcome any other down sides, but it does require some thought and planning.
    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 ↓↓

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    There are things you can do to speed up performance of azure the main one being ensuring only minimal data is transferred across the web and using stored procedures/pass through queries as previously advised

    Other things to consider - what bandwidth do users have for accessing the web? Have you got the right azure license for the data volumes to be transferred?

    I would also ask why you want to do this? Don’t get too enamoured with the web. Unlimited licences is not a valid reason, particularly if this is an attempt to reduce costs - it wont. You just swap one type of license for another generally more expensive one. Although if you are a registered charity, there will no doubt be discounts available.

    Valid reasons might be so users with non windows devices can use the app and/or be able to connect wirelessly. Another may be you need to connect a large number of users simultaneously- say over 100, although this is more to do with using a more powerful backend than access rather that the web.

    You mention linking to other apps. If these are web based then there are web based services out there which will enable you to draw down data from these apps - all depends if they have subscribed to these services. And again there will be a cost.

    You might be better dropping the idea of using access and subscribe to a web based app that does what you want.

  13. #13
    keviny04 is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2015
    Posts
    128
    Quote Originally Posted by Minty View Post
    Some of what @kevinoy04 is said above is broadly correct, but a majority of the slowness is caused by poor design e.g. loading forms with every record available.
    Bound forms still perform very adequately if properly designed, and many developers use SQL and Azure as a back-end without any issues, as they design with that in mind from the get go.
    Pulling in every record even with an Access backend is very inefficient.

    With respect to the queries he is wrong - ODBC will make a majority of queries run very efficiently as it knows how to provide the correct syntax to SQL Server(you can prove this by looking at the execution plans and data exchanged between the two), provided they don't include any local VBA functions.
    If they have any non native functions in them then all the records need to be pulled into Access to be processed, so if you use these they will need a rethink or moving to the BE.

    Where required the power of a server based query or view will more than overcome any other down sides, but it does require some thought and planning.
    Thanks for the correction on the point about the queries. I retired 2 years ago so I'm rusty and wasn't thinking straight. Access pulling in every record is, of course, an age-old myth, and I should know better. But moving to cloud computing could indeed be a world of hurt for whoever doing the move (sorry for the alarmist rhetoric again). For someone who has never developed for client-server environment, there is bound to be lots of work ahead. The OP asked if there were any articles on how to do this. It would be tough to write a how-to guide since this is really an art, not a science, as my own experience on this very task has shown. The only constant is the limited Internet speed most of us have. Wait till the Internet becomes 500 times faster, which will be in 10 years or less, and you could probably save a ton of work!

  14. #14
    keviny04 is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2015
    Posts
    128
    Btw, does the problem of the OnCurrent event of a subform getting triggered several times still exist? Back when I was working on remote databases years ago, that was a headache for me because an event running multiple times needlessly would wreak havoc on performance in remote data retrieval. I had to use the stupid workaround of using a global counter variable to limit the number of OnCurrent triggering to one. That gave me nightmares for days. You WOULDN'T know this to be an issue unless you started working on remote data.

  15. #15
    louise is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    112
    Thank you for this very helpful conversation! It has given me a lot to consider.
    Any other thought/continuing conversation on this broad topic would be welcome!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Moving Access to the Cloud
    By jeff@bennet-Tec.com in forum Access
    Replies: 3
    Last Post: 11-22-2018, 07:36 PM
  2. Replies: 5
    Last Post: 05-12-2017, 11:40 AM
  3. Replies: 8
    Last Post: 12-16-2016, 07:10 PM
  4. Access backend in the cloud - am I dreaming?
    By Gina Maylone in forum Access
    Replies: 5
    Last Post: 01-01-2016, 09:22 PM
  5. Cloud based Access?
    By Perceptus in forum Access
    Replies: 4
    Last Post: 12-30-2015, 05:05 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