Moving Away from Access As Frontend Advice

Hello, We are a retailer in UK and very simply our systems run across MS SQL Server for Warehouse/ Manufacturing functions & AS400 / IBM for Order Processing & ERP.

Up to now we’ve used Access Database & ODBC to bring this data together and so Access acts as the frontend application for our users (and some automated processes).

The users will have several access db’s running locally on their PC.

We’d like to move away from Access.

I’m thinking adding DB2 data connectors to our SQL Server (2016) and linking the AS400 data into this as a linked server for the data layer then building application layer over the top of this in some form of .Net.

Question is where do I start?

If I start visual studio I get a lot of options for new projects - WPF, Forms etc.and I’m not sure which one is most appropriate for building desktop application.

I know this is quite vague so feel free to quiz me for more information.

I just want to be sure I’m setting off down the right path.



So, you open up Visual Studio and already get confused by the many project options that you have! That’s a sign that you should immediately stop what you’re doing and return to the drawing board! It means you have no plan of what you’re going to do but are just trying to fix things as you start fooling around. That’s not the proper way to develop good software!
You will need an expert, but as those are costly, you’re trying it yourself. Fine. So, let me guide you to the proper path…
First of all, you will need to collect the documentation for your current system. You will need to map how data flows from one part to the other. It’s probably just a bunch of orders and invoices with additional customer data, product data, shop data and some other data that’s stored inside SQL Server and the ERP. You will need to know the exact layout of data and where it is stored, including the data format for each storage area.
As you want to move away from Access your focus would be just on that part of the data. But although you could create a new desktop application with DB2 data connectors to connect to the remote server, I don’t think that would be a secure solution. That’s because these applications will have direct access to your ERP system. This is a severe security risk!
Instead, you will need to define a data layer on a secure server that has access to your data. And all data transactions will go through this layer. This layer would just expose methods to the outside world for predefined actions and should not grant access to all data! It should not allow any client applications to execute random SQL commands. This is why you’d have to look at all data and start making a CRUD model. (,_read,_update_and_delete) The focus here is not on tables and records in the database but objects that represent this data, like orders, invoices, customers and whatever more. So if you read an existing order, it will be more than a record from the ‘Orders’ table. It might also have a user table, a list of products from the order and the invoices that are linked to it. So, it’s a collection of data that’s returned as a single object.

But you might want to reconsider making a desktop application. Personally, I think you’d be better off building a web application instead! That way, you would have no need to update every workstation and you have more control over the security. Users would have to log in using username and password or even more complex authentication systems and with an extra authorisation scheme you can assign roles to users to make sure they don’t become all-powerful. By also adding a RESTful service (Web API) it could also be used by automated processes for other applications to be built upon.
In fact, I would start building the Web API first around the data layer. Once you have it, you can develop web applications, automated tasks but also desktop applications and even mobile applications around it all.
Problem is, whatever you decide, it will take a long time to implement. It could easily take 6 months or more to implement correctly, even for a simple system. I’ve seen projects like this fail simply because someone thought they could do it easily.
You’re about to fail if you decide to continue by yourself and just pick some solution in Visual Studio without proper analysis.

Hi Lee, feel your pain mate.

Migrating from Access to the Web is a massive step. It’s a re-write pure and simple. Web apps have advantages but the desktop is far from dead. Yes installation can be a bugger, but if it’s all in-house for a few PC’s what’s the big deal. I don’t know your set-up or requirements but everything has it’s pros and cons.

The closest thing to Access in Visual Studio would be VB.Net WinForms. Trust me there are still plenty of VB developers out there. I just did a quick search and this mob can do it for you. I’m sure there are others too.

And if cost is a factor, you can always out-source to India. Plenty of talent at a third or quarter the cost of local talent. It would have be nice if Microsoft matured Access a bit more, but we should be grateful they didn’t dump it like Visual Fox Pro.

Don’t worry about WPF, but if you don’t know how to program don’t do it. Access hold’s your hand, and Visual Studio won’t. Access was designed to help you, and Visual Studio is designed to get our of a programmers way. Different tools. I believe File Maker Pro is the closest competitor to Access is still going strong and I believe they have Web enabled apps too. I personally have no experience in File Maker, but it’s worth a look at if you are planning to migrate.

.NET Foundation Website | Blog | Projects | Code of Conduct