Friday, September 02, 2005

Access/SQL:How to satisfy the underlying record

Even tho this is a Sql Blog, the following actually is related...

Many developers will often use Microsoft Access as a viable Front End to Sql Server instead of using VB or even VB dotNet. This is often a quick way to leverage rapid development. However if you've ever used Access you'll quickly find that it's not the friendly little IDE that it claims to be. When you attempt to leverage it's power as bound data application, you'll often find yourself wrestling within the clutches of it's Before Update, Current and After Update Events. I can say you haven't known how to really hate, until you've battled it out with these events. In a simple application it all works fine, in a typical Access MDB format, binding data to forms is common and relatively easy and painless. Try it with an ADP (Access Data Project).

The Before Update event can easily be avoided and replaced with a database trigger. Trigger's to Sql Server are what Before Update events are to Access. If your changes can be made server side, it's often the best plan of attack. But what if in your trigger you update other tables? if you do you'll most certainly be faced with the dreaded "The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source." followed by quickly seeing the data you just keyed into your Access Form disappear. Seems like it'd be easier to wrestle w/ the Before Update and Current Events. The practicallity of doing a trigger is that you don't need to make another round trip to the database. For Access the above error occurs because Access uses Sql's @@Identity to synchronize with the Front end. It's best outlined in KB article:KB275090.

My workaround? I created a temp table and filled it with the target table's PKID

CREATE TABLE #reIdentify (JUNKID int IDENTITY (1, 1) NOT NULL)
SET IDENTITY_INSERT #reIdentify ON
INSERT INTO #reIdentify(JunkID)
SELECT MyTablePKID FROM INSERTED
SET IDENTITY_INSERT #reIdentify OFF

Now I can have my Before_Update event occuring at the server level saving me client processing and battling yet another Access Bound Event.