Replacement for VFP… Is Access a Good Choice for Actuarial?

I started looking for a replacement Visual FoxPro ("VFP") since 2013, especially I noticed that Microsoft is going to cease its support on VFP 9.0 (released in 2004 and updated in 2007) after 13 January 2015. Although such technical support is not a concern to me (I never requested any support from Microsoft during my more than 10 years of experience using VFP for my actuarial work), I noticed that it would be more and more difficult for me to convince my clients (those who are not existing VFP users) to consider VFP in designing solutions for them.

When I did my research on the website, I came across many names that I was totally not familiar… Is "Lightswitch" mentioned in the official VFP website suitable? After studying its features, I concluded that it is perhaps a good application for an IT programmer, but it is too much for me in handling actuarial work. Basically, what I need in my work is to manipulate and analyze data, ideally using common programming languages like VBA and SQL. How about other applications like Lianja or Xbase++? They are "alien" to many clients (and myself as well!) and I am totally unsure whether they are vendors in Malaysia supplying these applications - furthermore, most clients will not consider these applications that are difficult to be maintained by their own users (as the solutions I designed mainly use End User Computing applications)  or getting supports.

Look like I don't have other choice except MS Access. Cheap & easily available.

Frankly speaking, initially I did not have a good perception on MS Access - I used to be a "debugger" for my team member, Mr. S, when I was the Valuation Manager in Company I. When Mr. S encountered errors in using his Access programs (developed by my genius ex-colleague Mr. F), he sometimes had no idea how to resolve those errors as Access did not give him enough "clue" on what was going on and what caused those errors. Differently, when we encountered an error in running VFP programs, VFP would indicate which line of SQL code causing that error and the error message did provide a good hint on how to resolve it. Recently I also found another actuary also has the same perception on Access.

In my recent project with one of my clients, I ended up using Access to design my solutions. Well, I have no other choice because their IT Security officer rejected VFP due to their IT policy - it was not allowed to acquire VFP as Microsoft is going to cease their supports. Well, I had to cracked my head how to make my Access programs more user-friendly, as well as having a similar efficient design structure as the VFP programs (which I have spent many years to establish). After doing some researches and referring to the two Access thick reference books I got from Kinokuniya, I have decided to use only the followings to develop my Access programs:

1. Visual Basic Application ("VBA")
2. Forms
3. Tables

It is a bit challenging writing VBA codes in Access, as it doesn't have a function to record "macros" as available in Excel - that means NO SHORTCUT, i.e. I have to enter ALL VBA codes by my own. Furthermore, the VBA language used in Access is not exactly the same as in Excel. Luckily, Access still allows me to write & execute SQL codes, in order to carry out processes like creating tables, selecting records, updating values, adding / removing columns, etc. After going through several times of "enhancements", I have established an initial design structure for Access, as well as leveraging the visual features (i.e. forms) available in Access that I didn't use in VFP - as shown in the screen captures below (modified from one of my actual solution to a non-actuarial user, to remove company specific info).

Users are required to key in username & password in order to use the functions setup in the Access program.

Users key in the run parameters (Start Date, End Date & Master Folder) and select required program procedures

Now I'm getting more and more comfortable with Access and I think Access is a good choice to replace VFP for actuarial work - as well as non-actuarial users. I shall share in more details in my subsequent articles.