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.)
Hi, Mike: I just Googled for "update arithabort" and found this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_5tys.asp. Note the following:
"SET ARITHABORT must be ON when creating or manipulating indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail."
You may user "ALTER DATABASE [databasename] SET ARITHABORT ON" to turn the option on for your database. This should eliminate the error.
Very, very cool Phil, thanks! Do you know how long that problem has bothered me? :)
Of course someone has to have SQL Query Analyzer or similar to resolve this as I don’t think it can be done from within Acess, but at least most if not all developers will be able to do it.