Legacy Access Database Upgrade and Support

Tax Database built in MS Access for the Falkland Islands Government

The Falkland Islands is an archipelago comprising of two main islands, East and West Falkland and 778 smaller islands in the South Atlantic, where approximately 3,400 residents live and 200+ companies do business. 

For over 25 years, the Falkland Islands Government Tax Office (FIGTO) has been using a combination of Microsoft Access and SQL Server to maintain their tax records with the Access portion of the database being stored in the “.adp” format. This format is considerably old, with Microsoft ceasing support for Access ADPs almost 10 years ago which has caused issues and concerns in several areas.

Because of these concerns and having the desire to further develop the database, FIGTO sent out a tender for companies to

  1. Analyse the current database and provide a recommendation as to the future of the database
  2. Consider a list of 50+ additional requests, changes and fixes and ascertain how these could best be implemented

Xlteq’s response was successful and resulted in being awarded the contract.

Working closely with the Head of Personal Tax and Pensions for the Falkland Island Government the team at Xlteq successfully deliver the new Access Database on budget.

When rebuilding the database, full optimisations were implemented including all the original features and enhancements.

The rebuild resulted in increased efficiency utilising robust database design, delivering quicker performance and reducing data entry mistakes.

Xlteq continue to provide ongoing database development and support for FIGTO. 

"We have worked with Xlteq over the last year to upgrade and enhance our Tax Database. Their collaborative approach and attention to detail makes them stand out."

Jodie Aldridge - Head of Personal Tax and Penrions at Falkland Island Government

Initial stages

In order to facilitate the brief, it was necessary to transfer the database onto our systems. This task was itself a challenge due to the age of the database. Initially a historic version of Access had to be loaded onto a dedicated computer and then a clean version of the Access database transferred across. The SQL Server information also had to be created before finally connecting the SQL Server to Access.

Xlteq reviewed the database and provided detailed responses to the list of changes and fixes with the overall recommendation that a complete rebuild of the database would be desirable for the following reasons:

  • Best Practice - Whilst most of the fixes and changes could be implemented in the current version of the database, several would require a considerable change to the current database structure in terms of how the data was stored and/or how the data was captured and displayed on the Forms. Future changes may also require a considerable development time if the database is left in its current state.
  • Best Practice – Several areas of the current database did not follow database best practices (e.g. tables with no indexes, normalisation). In order to update the database with these, considerable work would have to be done.
  • Security – Modern versions of Access and its associated file formats are more secure than the older versions.
  • Futureproofing – The new Access file format (accdb) has a better chance of being supported in future versions of Access. The adp version is no longer supported.
  • Futureproofing – It will be easier to make additional changes in the future using the accdb format than the adp format as this format should be available to most developers.
  • Speed - The current database has had many changes over the preceding years and there is a lot of code which has been re-written. There are instances of code which has redundant functionality, is over complicated or, in a couple of cases, does not appear correct. A re-write would tidy up this code, making it easier to understand and additionally can be written in a more efficient manner, speeding up the database.
  • Speed – The look and functionality of the database can be rebuilt, providing better functionality for the end-user, and so improving their work productivity.


The Menu Interface was redesigned to a much simpler, intuitive feel with similar options grouped together to make it easier to find the required action. New options were added to make it easier for the user to access forms directly rather than having to open other forms first.

All forms were rebuilt to provide a cleaner interface and a more consistent theme across the whole database (e.g. same font, font size, colour scheme, etc.).

Formulas were also rebuilt to ensure that no errors were displayed on forms which can happen when values are divided by zero or try to add “Null” values.

Example of redesigned form and formula improvement

Code Improvements

The VBA code used in the database had been built up over 25 years and included many fixes and changes and now numbered at over several thousand lines. In many cases this led to code which was hard to read or was superfluous to what was intended. In some cases, the code would perform the same action more than once or change a previous action before the effects of the previous action were completed. All code in the database was reviewed and rebuilt from scratch. This resulted in a considerable reduction in code size and complexity. 

Views and Queries

Due to the database having been created in an ADP format, a considerable number of queries and database operations were coded in the front end, which then were sent to the SQL database to be run as a Stored Procedure. Where appropriate these were recoded to run directly from the front end or to be called directly from the SQL Server without the requirement to be passed into a stored procedure first.

Several of the old Forms had multiple calculated fields (over 30 in one form alone) which would impact performance, as these fields are calculated when the form is opened, or records are changed. Many of these calculations were transformed into a SQL View and linked to the data table instead. This meant the data was ready to hand when the form opened, avoiding a costly calculation.


Reporting was built from scratch, not only providing a more uniform method to run reports but also increasing the options available. All reports are available from a dedicated reporting menu, allow filters to be selected, and can be presented in a native Access report, as an Excel workbook, or as a PDF document. Due to the way the data is now stored and linked; additional reports were also built which could not have been built in the previous version without a considerable amount of work. With an eye to future reporting, the whole reporting functionality has been built using a generic form and VBA code which can adapt to any additional filter requirements.

Importing Data

It was requested that data can be imported from a spreadsheet instead of having to manually enter the data line by line. This posed several challenges including:

  • Being able to read the Spreadsheet data and then check the data in the spreadsheet had no errors.
  • Load the data into a temporary dataset/table so that it can be manually checked and corrected where necessary.
  • To match up records in the spreadsheet with a record stored in a database.
  • To either insert a new record or update a current record in the database.

Administration Functionality

The new database was built with additional security features including the ability to limit each form to either be inaccessible, read only, or fully read/write depending on the user who is logged in. Additional roles can also be created to allow specific access to specific forms (for example a “Reporting” role was created to only allow access to the reports).

Hardcoded values in the old database (for example tax limits) were now moved into a table, allowing the user (with a specific permission) to be able to adjust these at any time in the future should they change.

Smart Fields

One important request was that some of the fields should become smarter, providing pre-defined values depending on other selections or displaying warnings if incorrect data has been entered. Examples of this include:

  • Date field providing suggested dates based on other date field values.
  • Checking dates fields have been entered correctly.
  • Checking if a field is a unique value within a group of other values.
  • Recalculating days between two dates entered.

Indexing and Normalisation

Indexing is where a unique key value (normally numeric) is assigned to a record to be able to identify each record.

Normalisation is where we replace field values which are often repeated (for example, the words “male” and “female” in the Gender column) with a numerical index which is stored on a separate table along with the word the index replaced (and possible additional information).

Both these methods were not fully utilised in the old database, meaning any searching, reporting, and data manipulation could result in a slower than necessary performance. The new database was designed with all tables indexed and normalised.

This results in a more efficient database design, quicker performance, and can reduce data entry mistakes as the user can now enter data from a list of options, rather than having to type in a value fully.

Contact us to find out more about our Access Database development and support.