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.

Comments

  1. husnaJuly 12, 2017 at 1:04 PM

    Great post Cheebs! For us who are still using VFP in our work, should we also start looking to move to other software? - Husna

    ReplyDelete
  2. Lim Chee BengJuly 28, 2017 at 11:04 AM

    Thanks for reading my article, Husna! :) At the moment, I still cannot find any good replacement for VFP, even MS Access. Even though we need to find an alternative eventually, at the moment I still suggest to keep it status quo if you don't have any potential candidates in mind at the moment.
    If you come across any potential good alternatives, feel free to share.

    ReplyDelete
  3. MacrosoftApril 8, 2024 at 3:38 PM

    VPF should replace, it is Outdated.

    Visual Foxpro Migration and Foxpro Migration

    ReplyDelete

Post a Comment

Popular posts from this blog

Other Ways to Prepare Prophet Model Point Files? Try FoxPro!

Image
If you are a frequent Prophet user, you must be definitely familiar with "Model Point Files" ("MPF"), i.e. the policy data that you compile in a specific format that Prophet can recognize as inputs for Prophet runs. If my guess is right, most probably you use Data Conversion System ("DCS") to convert the source data you download from your policy administration system (may be in various format, e.g. fixed ASCII / fixed width, comma delimited, tab delimited, etc.) into the required MPF, based on the definitions you specified in your DCS programs. In case your source data are divided into various files, you may need to carry out more steps to prepare MPF, for example: Open different source files in Excel Combine required fields from different source files by using commands (such as) VLOOKUP, SUMIF & MATCH. You may have automated this process by using VBA. Convert the required files into text files Use DCS to convert those text files into MPF  How...
Read more

How do You Setup Indicators in Your Prophet Model?

Image
In my previous post, I have discussed areas that you may consider in setting up input variables . Before I proceed my discussion on core variables, I would think that it is good for use to take a look on indicators - as indicators play an important role in setting core variables with various formulas (known as "variable definitions" in a Prophet model).  The funny thing about indicators is: it DOES NOT carry any formulas that you need to use in calculating a variable! Basically, they are just some texts that you can combine using different logical operators (AND, OR, NOT), such as "ORD_PAR AND SING_PREM" for a single premium ordinary participating product. 
Read more

How Should I Do My UAT? (1): Program / Spreadsheet / Model

Image
If you are working in the actuarial department of an insurance company, it may not be uncommon to you involving in various testing exercises, which are known as User Acceptance Testing ("UAT") in general. Some of us may think that UAT is only applicable to the policy administration system implementation project (Oops... Do I remind on the non-stop follow-ups from your Project Manager?), but in fact UAT is also needed for the programs, spreadsheet templates or actuarial model we use to perform various actuarial studies - whether they are developed internally or by external consultant. It is particularly important for those tools which we are going to use to perform regular studies. In my view, there is no one right answer for the approach to be used in performing UAT on program / spreadsheet / model - most importantly, the approach that you adopt should allow the required testing to be carried out in a structured manner, and it should not create too much "processes...
Read more