VIRTUAL DAN

VIRTUAL DAN

Notes from my travels around the internet

VIRTUAL DAN
  • The Pacific Northwest Solar Experiment
  • About this blog
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.

August 24, 2017 Dan Leave a comment

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.

 

 

 

August 9, 2017 Dan Leave a comment

Archives

  • December 2019 (1)
  • November 2019 (2)
  • October 2019 (2)
  • September 2019 (1)
  • August 2019 (2)
  • July 2019 (2)
  • June 2019 (1)
  • May 2019 (3)
  • April 2019 (2)
  • March 2019 (2)
  • February 2019 (2)
  • January 2019 (3)
  • December 2018 (2)
  • November 2018 (2)
  • October 2018 (2)
  • September 2018 (2)
  • August 2018 (2)
  • July 2018 (2)
  • June 2018 (2)
  • May 2018 (2)
  • April 2018 (2)
  • March 2018 (2)
  • February 2018 (1)
  • January 2018 (3)
  • December 2017 (2)
  • November 2017 (2)
  • October 2017 (2)
  • September 2017 (2)
  • August 2017 (2)
  • July 2017 (2)
  • June 2017 (3)
  • May 2017 (2)
  • April 2017 (1)
  • March 2017 (3)
  • February 2017 (3)
  • January 2017 (2)
  • December 2016 (2)
  • November 2016 (3)
  • October 2016 (2)
  • September 2016 (2)
  • August 2016 (2)
  • July 2016 (2)
  • June 2016 (2)
  • May 2016 (2)
  • April 2016 (2)
  • March 2016 (2)
  • February 2016 (3)
  • January 2016 (4)
  • December 2015 (2)
  • November 2015 (2)
  • October 2015 (3)
  • September 2015 (3)
  • August 2015 (2)
  • July 2015 (4)
  • June 2015 (2)
  • May 2015 (4)
  • April 2015 (3)
  • March 2015 (4)
  • February 2015 (4)
  • January 2015 (4)
  • December 2014 (5)
  • November 2014 (3)
  • October 2014 (5)
  • September 2014 (3)
  • August 2014 (5)
  • July 2014 (4)
  • June 2014 (4)
  • May 2014 (3)
  • April 2014 (3)
  • March 2014 (5)
  • February 2014 (2)
  • January 2014 (5)
  • December 2013 (4)
  • November 2013 (6)
  • October 2013 (3)
  • September 2013 (3)
  • August 2013 (4)
  • July 2013 (3)
  • June 2013 (3)
  • May 2013 (5)
  • April 2013 (2)
  • March 2013 (6)
  • February 2013 (6)
  • January 2013 (5)
  • December 2012 (5)
  • November 2012 (4)
  • October 2012 (3)
  • September 2012 (4)
  • August 2012 (3)
  • July 2012 (3)
  • June 2012 (2)
  • May 2012 (3)
  • March 2012 (3)
  • February 2012 (2)
  • January 2012 (1)
  • December 2011 (3)
  • November 2011 (3)
  • October 2011 (2)
  • September 2011 (2)
  • August 2011 (3)
  • July 2011 (4)
  • June 2011 (4)
  • May 2011 (3)
  • April 2011 (6)
  • March 2011 (8)
Powered by WordPress | theme SG Simple