Monthly Archives: August 2017

Microsoft Access and Web Api

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:

 

Upgrade Architecture
Microsoft Access Upgrade Architecture

 

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.

Crisis or Not?

Many Economists are making dire predictions about the next economic slowdown due to the increases in world leverage since the 2008 crash.

Bill Mauldin is the latest to publish an interesting commentary on this.   The main thesis of his post is he sees far more dangers than the Federal Reserve.

His biggest beef is with Janet Yellen’s recent quote:

Would I say there will never, ever be another financial crisis? You know probably that would be going too far, but I do think we’re much safer, and I hope that it will not be in our lifetimes and I don’t believe it will be.

Contrast that quote with this chart Mauldin included showing how much more the world is leveraged than in 2008:

crisisimage

 

So who is right:  Was 2008 a once in a lifetime economic experience, or is there another disaster looming?  As is true in most cases, hopefully the answer is somewhere in the middle.  But the leverage is something to think about.  In 2007, the GDP of the G7 countries (which is a decent approximation of the above chart) was 31 trillion.  In 2017, it is estimated to be 36 trillion.  In 10 years, the G7 economic output as increased just over 16%, yet leverage is now more than tripled.

So for an analogy, lets say in 2007 you were earning $31,000/year, and had outstanding debts of $4,000.  In 2017, you think you will be earning $36,000/year with debts of $13,000.  Would you consider yourself fiscally better off in 2007 or 2017?  How worried would you be if there was a recession on the horizon.

Some economists may question whether this is a valid analogy or not, but it does show the magnitude of how the countries fiscal health has changed.  Obviously Janet Yellen doesn’t consider that analogy totally on target,  based on her above quote.  I would sure like to see better evidence as to why she thinks we are so much better off now.

If I had to pick between the two camps in this argument, I would lean toward’s Mauldin’s thinking.  His quote:

Generals always fight the last war. The next crisis probably won’t originate in residential mortgage loans. It will come from somewhere else, and we have no idea whether the banks are actually ready for it.

I don’t get the impression this worldwide glut of debt greatly concerns the Federal Reserve Board.  Hopefully they are right.  If they are wrong, history will likely place the blame for any major economic downturn squarely on the Federal Reserve and all worldwide central banks.