Hands on: From Access to SQL Server

A couple of months ago, after demonstrating due diligence (that is, consulting the readership), I decided to take a look at connecting from Access to SQL Server. There are essentially three choices.

First, you can use Access to connect to an existing SQL Server database. This allows you to use Access to build forms, run queries, and add and modify data but doesn’t let you modify the data structure (add new tables and so on). Second, you can upsize an existing Access database to a SQL Server database. Or third, you can use Access to manipulate both the data and the data structure.

In the Christmas issue I looked at first option and since I’d already covered upsizing (the second option) extensively, I said that I didn’t intend to go into much detail. However, Nigel Hick then wrote in and raised an important point: could upsizing an Access database to SQL Server 2005 affect the VB code in forms?

Upsizing issues
It is an excellent point. VB code may behave as before ­ and the simpler the code, the more chance it has of doing so ­ but even forms without code can exhibit a different behaviour when the database is upsized.
For example, both Access and SQL Server have a Yes/No field. Access allows only two values: Yes and No, with No as the default. SQL Server allows three: Yes/No/Null, and the default value is Null.

Now imagine an Access-based patient record system in a hospital. It has a form with a Yes/No field labelled ‘Female’. If it contains a Yes, we assume the patient is female; if No, we assume male. The default in Access is No, so the administrators are used to entering a value only if the new patient is female.

The first problem is that when we upsize the default changes to Null, so we suddenly have a whole clutch of patients with no gender recorded.

That’s bad and VB can make it worse. Suppose we have some VB code that looks at the value in this field: if it finds a No, it allocates the patient to a male ward, otherwise it allocates the patient to a female ward.
This code was fine in the original version (assuming that female patients were correctly entered), but now it’s a recipe for disaster. If the administrators go on entering data in the same way as they are used to, everyone will be allocated to the female wards irrespective of gender.

It is worth noting that the problem has arisen here because the original code made assumptions about the range of values that are possible in a Yes/No field, specifically that if it wasn’t No it had to be Yes. That was a reasonable assumption under Access but is not so under SQL Server. Developers should, of course, always write code that makes no assumptions about anything, but in practice this is almost impossible.

The bottom line is that you need to be very careful when upsizing from Access to SQL Server.