Forum Discussion
Moving Away from MS Access
The organization I work for is wanting to move away from Microsoft Access databases where possible, and we have started exploring what tools our current Access databases could be moved to. Is there ...
Brian_Hayes I feel your pain Brian. I also agree with your comments about all the negativity and asking for more details on what it is you want. My company uses Access very heavily, with a fairly complex Access front end that was built in house over many years, and we'd like to move away from it towards a more cloud based front end. We already us SQL Server as the back end, so enabling users to have more flexibility and the ability to us a front end that doesn't require them to remotely connected to their work computers would be helpful. We have a number of SQL Databases that we connect to and use, trying to maintain all those with an acceptable response time over a VPN via Access proved to be less than idea.
As someone else mentioned we are looking at PowerApps and I just some time looking at the Microsoft Build conference to see what the have going on there, which is a lot. But as I mentioned and maybe is your case too, we have a fairly complex front end, to have to rewrite it from scratch would be time intensive to say the least. Some way to convert it would be ideal. At least then we could have something up and running in less time.
Although I was initially luke-warm on PowerApps (at the time they were being introduced, I had just co-authored a book on Access Web Apps for Access 2013, alas). However, I was recently induced to take another look at the current state of PowerApp development. It's now possible to implement a really useful Front End for your relational database applications IF they have a non-Access Back End. In other words, if your BE is in SharePoint, SQL Server/SQL Azure, or one of the other supported data sources (lamentably not accdbs) you can have the best of both worlds, a robust desktop FE in Access and a competent mobile interface running on a smart phone, a tablet, or in a browser.
I've only done two such PA apps so far, but I'm suitably impressed.
- Now I’m interested. Haven’t ever made a power app. We have a lot of financial data that is very sensitive and so we do not use the cloud. Can you use PowerApps with a SQL Server in house?
Yes, but with limitations.
First, you must have an Office 365 account which includes PA. I am not an expert on that, though, so I'll leave it at a general comment.
You have the option of many Datasources. Here's a partial list. There must be 50 or 60 in total.
Note that SQL Server is designated as "Premium", which means there is a per app/per user cost in addition to the basic O365 account. SharePoint lists are not and therefore incur no additional cost.I recently saw that MS plans a price drop soon, though. Licensing issues are always opaque with MS I'm afraid.
That said, there is also a data issue to contend with.PowerApps has a limit on the number of records that can be retrieved from a data source, depending on which data source and whether the function that retrieves them can be delegated (sent for processing on the server side, somewhat analogous to a passthru query, in a way). I'm going to make a hash of delegation, so again, a general comment. The default limit is 500 records, either in total in some situations, or in iterative calls to the data source in others. It can be increased to 2000 records. With SQL Server, that's more significant. Make sure you investigate this before committing.
I was able to finesse this by limiting the records I select to the most recent x days, say 30 or 90. That way I can see current data, but not historical data. For a data entry operation in a mobile app, that's generally adequate. I want to add new exercise sessions while at the gym, for example, and I really don't need to see any sessions from two years ago on the smart phone; I have an Access FE for that when I get home.
That's kind of a good illustration, as a matter of fact, of how I myself see their role. An extension for remote situations where limited processes are needed, but full data history isn't critical.- Thank you to EVERYONE for your responses and insights on this question. I actually have a limited knowledge of Access, but supporting databases somehow fell into my lap (aka I drew the short straw I guess). Now, the State doesn't have means to many resources to support Access, or to hire Access developers. So instead their "solution" is to get everything out of Access into something else. It's a project that's been ongoing for years and always flames out before anything changes. That said, I'd love to at least get them all to an SQL back end. But I've never done that before. Do any of you with SQL knowledge know of a tutorial or training on how to do this? The larger issue is that this particular area had their own "IT" area, and they chose Access for many things that were not good fits for Access. So the folks who developed the databases are all long gone and we're left to sort it out. It's a bit of a mess. I so appreciate all of you and didn't mean to insinuate that everyone was being critical of my question. That was me having a frustrating day, which I apologize for. Everyone on this thread has been nothing but helpful.
