Moving Away from MS Access

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.