Coding myself into a corner

coding myself into a cornerI spend an awful lot of my working life adding new features and improvements to a legacy database system.

A legacy system is an old method, technology, computer system, or application program, “of, relating to, or being a previous or outdated computer system.” Often a pejorative term, referencing a system as “legacy” often implies that the system is out of date or in need of replacement.

My particular legacy system is Microsoft Access. We use Microsoft Access to provide the front end for all our internal databases. The particular technology we use is ADP files which are only supported in Access 2000, 2003 or 2010.

Microsoft has already dropped support for Access 2000 and 2003, and support for 2010 is due to be dropped in 2020.

So why am I coding myself into a corner? Its simple for every feature or improvement I create in Access, I increase the amount of features I need to create or port to a new front end.

This is a very depressing thought, everything I create will need creating again. I am not saving myself work but increasing the amount of work I need to do again.

The bad news is that my employers like almost all businesses demand results and like a good employee I have been delivering them. I have been promised the mythical “when we are quiet” you can work on rebuilding the database front end. I know this will likely never happen so what are my options.

Do nothing I have warned my managers that this work needs doing and it is up to them to give me the resources I need. This is not an acceptable option. Firstly I am not future proofing the business, 2020 will be here before we know it. Also I am not developing myself as a developer, as the only experience I am getting is with Legacy technology that has expired or will do very soon. No one wants to employ someone who only has legacy experience.

Do something I need to keep delivering results and doing what is asked of me. Like any good engineer I should be multiplying my estimates by four, meaning that I have some time that can be used for looking at the bigger picture. This is a win-win option really. Employer gets a solution that is future proof, Employee gets valuable experience in up to date technology.

What do you think? Have you been tied to legacy technology? Why not leave a comment below.

My Love/Hate Relationship with Microsoft Access

For historical reasons I use Microsoft Access as the front-end for all my line of business databases.

Access allows you to create an ADP file that can connect to your SQL Server database. However Office has dropped support for this, so the last version of office that I can use is Office 2010.

Before I start hating on Access there are some good things about the platform. Users with very little knowledge can create a database fairly quickly. Development time is also fairly low as it doesn’t take long to create a new form, and add a few buttons which open different reports.

For the last 5 years or so I have built a range of different forms in Access, (and even if I do say so myself), I have got quite good at creating forms that users can be more productive with. Access has the concept of a subform which is a form embedded in a form, each form can be connected to a different query and can be connected to each other so it is relatively easy to create something quite powerful.

Access report are an easy way to create professional looking reports that can be sent out to clients, or used by management and they also have the concept of subreports, (same as subforms but embedded in a report), this means you don’t have to write one query to pull back every piece of data that shows up in a report.

VBA or Visual Basic for Access allows you easily do things programmatically, like run something if something is selected or a button is clicked. This is not the easiest programming language to work with, however once you have used it for a while you get used to its quirks. My favourite function is IIF, the syntax is fairly simple IIF(expr, truepart, falsepart), if expr evaluates to true the truepart is executed (executed can be as simple as displaying certain text in certain conditions), if it is false the falsepart is executed, and you can also chain them together which I have done to produce something very complex looking.

Now for the negative side of things. Access creates a binary ADP file, binary files can not be version controlled in git or subversion. This is a big problem as I want to keep track of every change I make. It also means that branching or working with others on big projects is also difficult.

One work around I have found to this problem is AccessSVN. This program takes an ADP and creates text files of every form and report. It also can create an ADP from these files as well, however I have had varying success with this feature so I prefer to just create the text files if possible. What I do after I have made a change to the ADP is to run AccessSVN, create text files and commit these to source control. You can then see what controls have been changed or added. It also helps with bugs as I have found that sometimes while testing access will apply a filter to a form, which will break things if deployed, which is very annoying.

Access can only do what access was designed to do. There are some advanced features that we would like to add to our databases. Things like sending emails or uploading reports to a website. This is not Access fault as it was never designed to be able to do such things but I am often asked about what is possible.

End of support for Office 2010 is approaching and I have plans to stop using Access but until then I need to keep supporting our existing front ends.

Overflow

Today I encountered a new error.

error

Run-time error ‘6’: Overflow doesn’t really tell me much. The error was occurring in the Access ADP front-end of our main database. It was only occurring for one particular Id number which was really confusing me.

First thing I tried was to remove the most recent changes I had made, this made no difference. So time to look at the VBA code that was run just before the error.

Dim iPropertyId As Integer
iPropertyId = DLookup(“PropertyId”, “Survey”, “[ProjectNo]=” & Me.ProjectNo)

All looks good, even running the Dlookup query in SQL Management Studio threw no errors.

SELECT PropertyId FROM dbo.Survey WHERE ProjectNo =

Next thing I tried was explicitly setting iPropertyId to the problematic Id, still the same error.

This made me look at the error message again. Overflow suggest something being too big, but what could it be.

Integer has a size limit of 32,767 and the value I was trying to pass into iProperty was 32779. This was what was causing the problem, my database had grown to such a size that Integer was too small, I needed to change it to a Long Integer. Long has a size of 2,147,483,647 so my database should keep working for a while more.

The famous programming help website Stack Overflow is named after such an error. This error shows how difficult it is to decide what variable type to use, as you don’t know how quickly your database will grow.