Migrating database application from Microsoft Access to .Net

Question

Hi,

My company uses a pretty complex database application that we developed ourselves with a Microsoft Access Front End and a SQL Server Back End. The features and functionality work quite well for the way we run our business, but we have started experiencing some performance issues as our number of users and the amount of data we are managing have increased. I have some ideas of how we can optimize this existing application to allow for better performance, but before I got started, we were purchased by a larger company. While they like they way our application works, but they have their own performance, security, and scalability concerns.

Specific weakness of our current application they pointed out are:

1. Front End database has table-to-table links for every backend table that exists in the SQL Server
2. The Record Sources used to populate our forms directly reference linked tables in the SQL Server
3. Too much code behind our forms.

They would like to consider scaling our application so that it can support several different companies that are similar to ours. This would likely involve moving the SQL Server Back End to the Cloud. They believe that rather than trying to optimize our Access application to mitigate the weaknesses mentioned above, that we recreate the application from scratch for a different platform. Their suggestion was that we consider Microsoft .Net as the platform, and C# as the programming language.

I have no experience with .Net development or with C#, but I am willing to try to add this to my skillset if it seems that this is a good approach to building a better performing, more scalable, and more secure application. I will probably start by reading a book on .Net Development with C#.

Before I dive into this, I figured I would ask if anyone here might be able to give me a quick rundown as to if/why .Net might be a better platform than Access considering the concerns we are hoping to address.

Any advice or suggestions will be greatly appreciated.

Thanks and best regards,
Paul

Answer 1

Number one advantage for moving to C# is combined with Entity Framework Core to work with a backend SQL-Server architecture is C# has more functionality, less constraints that using a ms-acccess frontend. Couple this with better security and performance.

Now with that said, before moving to SQL-Server ensure you have proper relations setup by porting data to a local SQL-Server database, perform load testing coupled with using in SSMS (SQL-Server Management Studio) SQL Server profiler and Database Engine Tuning Advisor to ensure there are proper indices for the operations to be performed in your application.

Dependent on if this is web or desktop coupled with what is expected of the user interface for web the obvious choice is ASP.NET Core while there are several models to select from which should be figured out based on requirements of the app. For desktop, consider using .NET Framework Core, C#9 unless there is a plan to move to Visual Studio 2022 which in the case C#10.

Beings this is your first time in C# I recommend using ReSharper (Google it) to assist with writing better code.

Then there should be a written out migration plan for both code and database. For the database migration, clean up data, map ms-access types to SQL-Server types.

Go OOP meaning in simplistic terms, your form code should be minimal, break out business logic and database logic into proper classes and possible in some cases into separate class project.

I's well worth the effort but keep in mind there will be a step learning curve if never written any C# code below.

That's pretty much it in a high level view of what to do.

And one last thing, consider writing unit test which takes time to master yet well worth the effort in the long run.

Answer 2

This is a big topic. REALLY big!

The issue is many, but one will surround how much code, time and investment has been made into the existing project?

So, say someone put in 4 months per year. But, then say you been running that application for 12 years.

Well, that’s 12 x 4 months = 48 months (5 years of development cost).

So, this is one of those how high, how big, how far kinds of questions.

You also have to take into account increased costs.

In the hands of a good Access developer, you be hard pressed to keep up the rate of development you get with Access compared to another platform.

It is REALLY good. Stunning good.

So, I would consider the situation with a lot of thought. Access simply reduces the development cost.

However, it comes down to what the final goal here is?

In other words, maybe the goal is more features, and maybe that goal involves say consuming web services. (A weak point of Access).

So, maybe for example, in place of some interface to QuickBooks, the company is now adopting the cloud based version of that accounting package. Now, all of a sudden it going to be rather hard to interface Access with that accounting package.

The next issue?

It really depends on the scope and breath of the application.

You can’t just out of a blue start developing a serious business application in any platform, including .net without a good skill set.

In fact, the skill set can be outlined like this:
Page-Jones, Meilir. "The Seven Stages of Expertise in Software Engineering", American Programmer, July-Aug 1990

• Stage 1 Innocent (never heard of the product)
• Stage 2 Aware (Has read an article about X)
• Stage 3 Apprentice (has attended a three-day seminar)
• Stage 4 Practitioner (ready to use X on a real project)
• Stage 5 Journeyman (uses X naturally and automatically in his job)
• Stage 6 Master (has internalized X, knows when to break the rules)
• Stage 7 Expert (writes books, gives lectures, looks for ways to extend x)

So, on your team, you want at least level 3 or more, and SOME on the team at least at 4-5 level.

Software development like playing music, or any other skill takes time and investment.

Furthermore, .net assumes that you the developer have a higher skill set and level.

I mean, you can deliver pizza with a bicycle.

Then, maybe volume increases, and now you use a car.

And then maybe you need to supply the local sports arena for football games. Now, you might need a big rig freight truck. But, that big rig truck now means special class licensees, an air brakes ticket and certification, and perhaps more.

So, as you scale up in software, then the ever increasing efforts (and costs) will also result.

So, while the cost of developing with .net is significantly higher than using Access?

Well, you can justify that increased costs, since it is assumed you doing more work, and attacking greater tasks with more complexity.

So, while that bicycle was easy, using a big freight truck, or say a helicopter to deliver things is MUCH more complex.

Maybe all of a sudden you need 2-3 developers. And again, .net let’s all those developers work on the project at the SAME time.

This means simple things cost more, but you have more developers, larger budgets, and more resources. And you have more reasons for that extra cost.

Access is a RAD tool (rapid application development). It is MUCH faster at creating software then most tools

Toss in that integrated reporting system? It really is an amazing setup.

So, you can’t just jump into software and a whole new platform without the above considerations – especially the skill levels required.

I like the pizza delivery example. Perhaps even better is building a dog house. Then maybe you tackle a shed for your tools.

Ok, now what about building a garage package! Now all of sudden, you talking about foundations, cement, electrical wiring.

And then how about a house?

And then how about a sky scraper?

But, they are ALL just construction projects, right???

All of a sudden, HUGE increases in skill sets are required as you climb this ladder.

When building a tall building, then all kinds of new skill sets are used. (And big dollars just for the Architecture firm).

Software is really like building things. I mean, building that dog house and a real house? Well, you just need hammers and wood, right?

But, as you can see the larger and more complex, then the greater skill sets required.

In most cases, I actually do NOT recommend re-writing good working Access applications.

I think adopting and building a customer web interface to that system is money better spent then JUST a re-write of the system.

In other words, look at what business process(s) in that application can be pushed out to customers.

(Why phone the company to get some project status when the customers can just log on and get that information themselves?
Or project status, project approval? Again, why have customers phone you, you fire up Access and then get that information for the customer? Let them do that! Packing tracking anyone?

So just a re-write gains you very little, unless there are new feature sets you require.

Or, you have bottle necks that prevent the current system from dealing with new business rules and requirements.

Or, as noted your developer team is growing and Access does not take well to multi-user development. Access can work with a source control system, but it not all that great.

So, while a helicopter or a freight truck is better at delivery?

Well, all of sudden everything is more complex, and more costly.

But then again, all those extra costs are justified.

You have to decide how well the application you have now serves you.

Software is an engineering process, and building software in .net will cost you more.

From Access to .net, I see costs often about double, but 3x is even better.

And if you going from Access to web? That’s easy 5x or even more the cost.

I don’t want to discourage you here, but you have to take into account costs and time and what kinds of budgets you have here.

And, VERY deceptive is that cumulative cost in the Access application can add up fast. A bit of changes here and there? Not much.

I know of some access applications that EASY represents over 1 million dollars of work.

To replace in .net? Well, now you talking several million dollars!!

I point out a month here, and a month there. But do that over 12 years, and you now have a rather high cost application here, and it not all that low cost to re-write.

And with that higher costs? Then you need and better reasons to justify those costs.

I would also give “big” considerations to jumping past the desktop, and going web based all the way. But, it will depend on the goals, IT infrastructures you have.

All the best, good luck!

I am of the view that a good working and well-built Access application should not “just” be re-written into .net unless specific goals and reasons for doing so exist.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer 3

No doubt that C# would be a better choice for an application than Access in terms of raw speed, flexibility and extensibility. C# simply is a more powerful language than the VBA you are using now in Access and it has access to 1000s of more libraries to add functionality. But with power comes responsibility. C# and VBA (based on VB.NET) aren't the same languages and so you'll have to learn a new language. If you have no prior C/C++/Java training then this is going to be a learning curve. You might do good, unless you are a faster learner of languages, to take training courses to get you up to speed quickly. Surely your company would pay for that. Online videos and PluralSight are good resources but nothing beats a training course. I'm sure you can find some that are a couple of days to a week long. But expect to take potentially a month or more to get comfortable with the new language.

The harder aspect though is going to be the UI side. Since you're using Access now I assume all your users just start Access and go but that isn't going to work anymore. You'll need to decide the type of UI you want to build. Bear in mind that if you have no UI experience then this is yet another learning curve you'll have to overcome. Do your users/company want to install your application on each machine that needs it? Do you plan to use Windows auth? How often do you expect changes to the app? The answers to these kinds of questions determine which UI route you'll take. If you need something quick and dirty then Windows Forms is simple, LOB-oriented and quick to pick up. Using ClickOnce you can quickly deploy and update client machines. But if your app is going to be rapidly changing over time then having users update constantly is going to be an issue. In that case a web UI is better. Web development is complex and requires yet more learning to do. It also requires a web server. But hosting the web app on a server means you can make changes to the app without having to deploy to client machines all the time. You should also consider local/remote user scenarios. Ultimately this boils down to business requirements more than technical requirements. All else being equal then Winforms is probably the closest equivalent to Access but you'll be building the entire UI by hand (like you did the Access Forms). If you need reports then that is completely separate.

Access is acting as your front end and it makes binding to your backend really easy. That isn't going to be so simple in another technology. If your database is really complex then you're going to be spending a bunch of time building screens to read/write data. This is simple but tedious. That also means you need a solid data access technology (Access was handling this for you). Yet more learning there but not so bad. Maybe start off with Entity Framework as it is built into MS tools and there are plenty of examples. It'll handle the SQL queries provided you build your models correctly.

Personally I would recommend that you start by learning C# and picking a UI technology to use. Then carve out a small piece of functionality (say product management) from your existing Access system and put it into your new app. This will allow you time to set up the connection to the DB, get some UI stuff figured out and get some answers to some questions you may have. Once you have the small piece of functionality working then get it deployed and get some feedback while you work on the next piece of functionality. As you get feedback and identify issues merge them into your next set of work. As you iterate over this again and again you'll eventually get to the point where your app will have more of the functionality than Access and you'll have (hopefully) resolved any lingering concerns. It then becomes a matter of migrating the remaining functionality over and deprecating the Access app. This could take months or years depending upon the complexity of the app.

Answer 4

Hi Karen, Cooldadtx, and Albert,

Thank you very much for your detailed responses, and I apologize it took me so long to follow up. I am happy I posted this question. This is a lot to absorb, but my takeaway is that it will be worth my effort to (try to) add .Net and C# programming to my skillset.

It will not be my decision whether we will migrate this application or not, and it is not expected that I will be the only developer. The company that purchased us has far greater resources than we have had previously. My understanding is that their intention is to buy several companies with similar businesses to ours, and to ultimately use the same software to manage all of them. Of all the companies they have purchased so far, they like the functionality our our Access Front End / SQL Server Back End, and think that it is worth scaling it up to run the business group they have in mind.

I feel like I am in a good position because while my current (lack of) a .Net / C# skillset would only put me in stage 2 (Aware) on the Melir Page-Jones scale, my long term and continued involvement with our Access / SQL Server application as it has been developed over the years to satisfy our business needs would (hopefully) make me useful in the migration process, even if I am not the most skilled developer participating in this process.

I think I'll make an effort to start learning C# and .Net. I will start as CoolDadTx suggested, choosing some small feature and attempting to implement it using .Net and C#, trying to get the hang of at least one UI option and how to connect it to our database. If I can advance my skillset to a stage 3 or 4, I'll at least be able to have more intelligent conversations with the IT folks from the new owners.

For starters, I will need to get the development tools to start making this effort. I see that there are a few options for Visual Studio Subscriptions - some free and others that get progressively more expensive. If my initial goal is just to start getting my feet wet with .Net and C#, I'd be very interested to know what subscription level any of you think would be best for me to get started with. I have no issue paying a monthly fee if the free versions have limitations that will make my learning effort less productive.

Thanks again and best regards,
Paul