I use MS Access from time to time, and for what it is, it is a pretty nice tool. However, there are a few things I’d really like to see added (subject of a future blog) and there are a few bugs that I run across from time to time that really frustrate. For example, I just ran across one of them.
Let’s say you have a simple table in a SQL Server database and you have an Access Data Project (an .ADP file) pointing to that database. Let’s say that table is called tblPeople and it has fields ID, Name, and Email. Further, let’s say we want to require the values in the Email field to be either 1.) NULL, or 2.) Unique. Of course you can’t set a constraint for it to be unique because then it has to be non-NULL. But, you can create a calculated field, let’s call it EmailKey that refers to the Email field if non-null, or refers to the int ID field converted to character when Email is null, i.e.:
ISNULL([Email],CONVERT(varchar,[ID]))
Then, create a UNIQUE index on EmailKey, and everything works great!
Great that is, except for when you try to add or edit a record from within Access and then you get this following error message "UPDATE failed because the following SET options have incorrect settings: ‘ARITHABORT’":
Arrggghhh!!! Perchance anyone know how to fix this, or it is just something I have to wait for MS to fix? (btw, this problem has existed for several versions of Access.)
