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. (https://en.wikipedia.org/wiki/Create,_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.