Feb 25 2020 10:27 AM
Feb 25 2020 10:27 AM
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 a standard for what works best (Powerapps, Sharepoint, etc)? What have you found to be a good resource for guidance on this topic? Thanks!
Feb 25 2020 01:28 PM
Okay, so you're asking a bunch of MS Access folks what options they recommend for abandoning Access? My first reaction is that nothing out there is a realistic alternative to Microsoft Access relational database applications at the moment.
In the "smart device" arena, i.e. apps that run on a phone or other device, PowerApps holds a lot of promise, and I know that Microsoft is pouring a lot of resources into it. Maybe look at that to support that kind of purpose. As far as desktop Relational Database Applications go, though, PA are a distant second as things stand today.
SharePoint is sort of like comparing a Swiss Army knife to a precision machine tool. Yeah, they both cut stuff, but if you want to create something useful, you kind of want to go with the tool that's designed specifically for creating something useful.
If you really want to go to the browser-based app arena, I'd pick a technology that supports it. And there are a huge variety of options there.
Feb 25 2020 02:44 PM
@George Hepworth yes I'm asking MS Access folks what options they recommend, because I'm being told that's the direction we're going. So who better to ask than people who know Access? The catch is that it has to be a Microsoft product, as our contract is with them. I appreciate your input. Have a great day.
Feb 26 2020 01:10 PM
When I hear people / companies say they are migrating away from Access I often ask what they are using Access for, and what problem are they trying to solve by eliminating it from their environment.
The common responses for usage are this:
1) Store Data (ie: using the .MDB/.ACCDB format as a data respository)
2) Access to data (not the application, but gain visibility to data that is stored in a non-MSAccess repository)
3) As UI to data (Forms, Reports, or Queries against data from all sorts of repositories)
The common problems being sought to fix are:
1) People are creating inefficient queries against data they don't understand. (this is not an MS Access issue by the way)
2) Data is not secure.
3) Too many silos of data (ie: corporate data in stored on Jacks or Jills PC and they went up a hill for vacation).
If you are using the .MDB/.ACCDB file format as a repository to store company info (Usage 1 and Problem 2 & 3), then don't! Use SQL Server. The Access (aka: JET or ACE repository format) is not a up to par for corporate data. It does fine for small businesses, many small business can work just fine within the constraints of security and size that the JET/ACE format limits us too, but once your needs go beyond those limitations, or you just need a standard repository to use across your company, the JET/ACE (Access) format should be migrated away from. Again, SQL Server Express is a good first step. Then SQL Server, or Azure SQL will take you to all ends you wish to travel to!
If you are using an .MDB/.ACCDB file to LINK to data sources so you can see it or do ad hoc queries with it (Usage 2) ... Great! ... there really is no substitute for what MSAccess can do. There is not a need to migrate to some other tool to fill that purpose. Also, if the data is stored in SQL Server, and it is accessed using Windows authentication, then Problem 2 is a myth. If you are doing a lot of analytics/reporting on your data, PowerBI using a live connection may be a good choice (but its definitely not an ad hoc query tool - and wont eliminate Problem 1). If you DON'T use a live connection to your data, you are then storing all of that data in a PBIX file (thus NOT eliminating Problem 3), and your data will need to be updated in order to keep your reports "fresh". If you are hell bent on ditching Access for ad hoc queries and visibility of data - maybe SSMS, but that is a "higher level" tool to use and you really don't want to install SSMS on everybody's machine.
If you are using MSAccess to build UI's for Line of Business applications (Usage 3) that access data stored in non-MSAccess repositories (ie: SQL Server, Oracle, Azure). Then .. Visual Studio is a development platform you can use to build C#/VB applications to fill your need. But keep in mind, VS is a "higher level" tool that should not be on every workers machine -- in short, its typically an IT tool. With that, IT will likely not want to build (or have the time to build) every utility you can dream of to help your (or a small groups) day to day life, thus the need for Line of Business apps that Access can address quite well - even if the LOB app is created by IT.
So where does this leave us with respect to eliminating MSAccess?
- You may prevent Usage 1 and Problems 2 & 3. But, honestly, MSAccess *may not* be your issue with those two bullets. If you remove MSAccess, people can (and likely will) use Excel for Usage 1 and still nurture Problem 2 and Problem 3. Then what, are you going to eliminate Excel? You definitely need to address Usage 1 and Problems 2 & 3, but eliminating MSAccess is not have as much "punch" in doing that.
- You remove a valuable tool to perform Usage 2. MSAccess is a great tool for Usage 2. Why force users into using Excel or PowerBI when what you really need is a Linked Table object.
- You complicate Usage 3 by increasing the level of expertise require to help people create LOB applications that help them do their job.
- Problems 1 or 3 go unsolved, and likely is placated into thinking problem 2 is fixed, but if you have access to the data ... you have access to the data whether you get to it through MSAccess, Excel, PowerBI.
This task you seek to perform of migrating away from MS Access may not yield what the people directing your work may want. Please know, that I am not saying you *shouldn't* do something, that is entirely up to the business you are serving, but in order to serve them well, ensure they are implementing the right project to address the needs MSAccess fills and the problems you are trying to solve.
Mar 09 2020 04:35 AM
Well this is an interesting "question"...but as stated is incomplete.
First you have to put down what you are using the Access for and what complaints you have that forces you to abandon ship.
The real answer is "how much money" is Access costing you and "how much money" you are willing to spend to get away...if someone just said "just leave Access is crap" ... let him/her show some alternatives...there are plenty but always there is a cost...
Mar 11 2020 10:11 AM
@tsgiannis you know, I came here for help and all I've gotten is different responses telling me I either am wrong for wanting to move away from Access, or people telling me I asked the question "wrong". I thought this was supposed to be a support and help community. I'll just take my questions elsewhere. Good day.
Mar 11 2020 11:27 AM
Sorry you feel that way! I can assure you that the intent of my reply was indeed to present options and insight. With limited context it is often hard to tell at what phase a questioner may be at in their quest for information.
So, with limited context, questioners get advice that may go well beyond what they are looking for! Kind of like sitting at dinner with cranky grand parent and you ask that one simple question, "Would you like your lemonade refilled?" .... and two hours later, you still don't have the question answered with the specifics you want, but you decide to refill their lemonade anyway as you listened about the days kids made lemonade from hand picked lemons on the corner and ate blueberries right out of the bushes on old man Johnsons farm.
I do hope you are able to address the needs you have. We have found that PowerBI is addressing the role Access used to fill with respect to analysis and reporting. SQL Server has replaced the use of Access as a repository. However, we have not been able to eliminate Access for its other uses, and at this point we likely won't as the sentiment toward Access has changed since we have implemented PowerBI and mandated all data be stored in a SQL Server repository.
Mar 12 2020 09:53 AM
I don't actually see anyone saying it is wrong to move away from Access. If anything, we're saying that doing so out of antipathy for Access is probably short-sighted. Access fills a niche that is really hard to fill otherwise.
Note that several alternatives have been suggested, including PowerApps, PowerBI, and SQL Server. I even suggested there are non-Windows approaches involving web-browser based technology.
I did succumb to the temptation to point out that asking Access developers what they should choose instead is kind of ironic. "I know you've been driving Fords for thirty years, but what do you wish you had been driving instead." "I know you like ice cream for desert, but what kind of cake should you be serving instead." See the similarity? If the question had been directed at FORMER Access developers, and had been phrased as "what kinds of advantages did you gain from choosing a different platform?" it wouldn't even have come up.
Mar 13 2020 06:02 AM - edited Mar 13 2020 06:12 AM
Just because you don't like the "answers" doesn't mean they are wrong.
Lets take a simple example.
Some years ago i read a book about migration of a Simple Access Application to a Web equivalent (ASP.NET i think i can't remember) .
It was around 300- 400 pages where the author took step by step the whole procedure from migrating the tables to SQL using Entity framework, designing the web "forms",the reports and so on...the surprising fact was the Access was a simple few forms - few reports application nothing heavy by any means...just a simple just over the beginner level template ...yet it took its effort ...it took its time...
SO yes you can move away from Access any time and there are great alternative...you want desktop ..then .NET or Java fills the blank perfectly ..you want web ..even better PHP/JS/.NET and a ton of frameworks are here to help for the UI...for the BE is much "easier"..just make a pick MSSQL/MySQL/Oracle/IBM/Firebird ...there are just too many options.....either paid or free.....
Some years ago my boss was discussing the transferring of the BE of a huge Access application to SQL...i replied NP...just "close" the IT department for 1 year ( i was the only "real" developer and i was also the system administrator)...let me transfer the table/queries and then optimize the whole application in order to use SQL efficiently...of course he denied ..saying that i was exaggerating and others have done it just by using one of the utilities that are in the "market" ...and its so "simple...we just migrate and we fix problems as they pop up".....i refused to take this lonely road.the whole idea along with the proposed planning was dumped...a few years ago i left the company...till today they are using Access and unless something magical pops up they will do for the next 20 years for sure.
So as i said in my previous "you didn't liked" post...nothing is holding you back ...the word "impossible" simply doesn't apply in these cases... if you are the manager you just have to train/enhance your team to do the job...if you are the boss you just have to hire some people to do the job...if you are the developer/administrator you just need to calculate your extra hours you are going to cost to your company to take such a task .....so as i said is all about money...either in explicit or implicit form but is money ....if you are not ready to spend/charge or whatever simply you will come back here or in another "friendlier" forum and find out the same fundamental truth.
Mar 13 2020 06:47 AM
I appreciate all of the replies and suggestions. I'm not choosing to move away from Access. That decision is being made for me, so I'm left to come up with alternatives. You have given me several good ideas. Thanks everyone.