Moving Away from MS Access

Brass Contributor

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!  

70 Replies

@Brian_Hayes 

 

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.

 

 

 

 

@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.

@Brian_Hayes 

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. 

@datAdrenaline thank you for your insight.  Appreciate it.

@Brian_Hayes 

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...

@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.

@Brian_Hayes 

 

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.

@Brian_Hayes 

 

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.

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.

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. 

@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.

@CJ_Butcher I have to say this is one of the best forum discussions I've ever read on this never ending question; how do we get away from Access.  I appreciated every response immensely.  I am constantly perplexed at how often businesses are unsatisfied with Access.  Even with telling them how hard it is to get off of Access, they are all in denial and just want an app from the Apple/Microsoft store that does everything for them.  I think it's a combination of it being a Microsoft app, that its VBA based, and that it has limitations that invites the criticality.  If something this magical has limitations it just creates suspicions for some reason.

I have decided to keep the many Access front ends we have (which took over 10 years to develop) and move to SQL Server backend.  Because they are linked tables its no different to the user as well as the forms.  We'll be implementing PBI and SSRS eventually... and thats ok!  No two businesses are the same.  Just be patient.  Microsoft wants all businesses to succeed and they make incredible things.  Upgrading our network infrastructure fixed all of our corrupt Access files, so you may want to get that checked out.  I hope you find something that works for you.

@Fatire 

As an Access developer from it's first version, I have to agree.  Moving the backend to MS SQL Server solved all problems with mdb corruption and it is now very fast.  VBA is a great environment IMO, and Access makes a great front end that is easily customized.  My only wish is that they would provide some sort of iOS application that could import some basic MS Access forms and have an ODBC connection to SQL included in that import.

@CJ_Butcher 

 

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?

@Fatire 

 

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.

GeorgeHepworth_0-1625761203197.png


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.

@Brian_Hayes 

I can understand your frustration!  I also have developed with MS Access since version 1.  Our small company has many lines of associated VBA code and 50+ custom tables linked to SQL Server as our backend.  So, I have to agree step 1 is to move all data to SQL Server and link the tables using Access as the front end.  The only other issue to watch going forward is if you have lots of VBA code, you will need to modify it for 64-bit Access which MS is pushing harder now.  For any new front end development, use 64-bit as it eventually will be all that is offered vs. 32-bit, assuming you want to use the newest Access versions.  Best of luck!

@Brian_Hayes 

 

First, we all have been there at one time or another. Frustration is part of the environment, I suppose. And for my part, I should have been less critical. I apologize.

 

"So instead their "solution" is to get everything out of Access into something else."

That's a common response, I'm afraid, all too common. It reflects a couple of problems, really.

 

First, Access is very easy to use, and that means a lot of less than professionally developed Access "databases" are created. That can create the impression the problem is with Access, when the reality is that the problem was lack of training and experience.

Second, replacing Access is seldom easy or cheap. I've seen cases where a lot more money was invested in the replacement software than would have been needed to simply hire a competent person to address the shortcomings in a particular Access solution.

 

So, while it is understandable that the impulse is there, the reality is that the replacement is probably going to cost more and take longer and no one will be satisfied.