I have a number of old Microsoft Access databases that I maintain for clients, and I shudder anytime a client wants to make changes.  Since I live happily in the world of c# and sql server,  going back into old VBA code and DAO is getting more and more painful.  Understandably these clients dont have the budget to upgrade their app, and it works fine in Access – Microsoft Access does have its place in the world for quickly building and prototyping apps, and when you deal with clients with pretty limited budgets, Microsoft Access still has a place in my toolbox.

Recently a client asked me to upsize his Access database to SQL server hosted on a cloud server.  This is usually a relatively simple task, and I got it up and running in a few hours.  Unfortunately, some of his entry forms (which have subforms) take a long time to render due to the amount of data Access is pulling across the web.  So I got some more budget to optimize the app, and started dreading going in and doing major surgery on this app and getting mired in VBA and DAO.

I want to transition this client to web based entry forms on a WordPress website, and eventually toss out the Access front end – and here I am spending time changing what I consider long-term throwaway code.  So before I started coding, I kept thinking to myself there must be a better (cheap) way that would allow me to code in C# and set the foundation for future architectural improvements.

First off, I decided that I have to change these slow complicated entry forms to unbind the controls from the tables,  and hand code all the database access in VBA, calling pass-through queries or using ADODB.   Before I started this, I wracked my brain for an alternatives.  That’s when I struck on the approach I liked – as shown in the diagram below:

 

 

First, I built some plumbing and VBA methods in my Access app to handle converting from and to JSON format.  I found a nice VBA / JSON library (thanks Tim Hall!) on Github that helped with this and saved me a bunch of time from writing my own JSON Formatter.  This will allow my Access app to communicate with an API.  I then created a .net webclient wrapper that I exposed to COM so that Access can see it.  Then, I changed my DAO reads and docmd.Saves to API get and put requests, using json built from the form fields.  These calls from my Access to a Web-API app route thru the com wrapper nicely to get and save data from the API.  After getting this plumbing configured, which wasn’t a huge deal, I got it working and it is working great.  The architecture will be reusable across other Microsoft Apps that I maintain, and the performance is great.  Initially I was a little concerned about the cost of jumping in and out of managed code for each request, but that really isn’t an issue.

I like design this because it keeps my from having to write in old languages and data access methods.  This also MVC principles to Access; I am really only using Access as the view engine.  Later on, I can use the same models I built in the Web API project to drive views in Angular, mobile apps, or any Javascript framework of choice.   I think this is a very affordable way to start transitioning code bases from Access VBA to .Net, with limited additional cost to the client.

So I am optimistic this is the solution for most of the Access applications that I write.  It should be an affordable solution to migrate these existing apps to a cloud based client server or SPA  application.

I have always been a fan of Microsoft Access, but I am afraid this brand new web enabled world, it is showing its age.  Hopefully, this infusion of technology will help extend the life of my Access apps, and provide a nice affordable solution to make them ready for  the future.