Re: Binding Access Forms to a SQL Server Data source : View Or Sto

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Malcolm Cook (mec_at_stowers-institute.org)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 15:17:00 -0500

I've been lurking with interest...

> It has been about 2 1/2 years since I had this set up this way, so I can't
> remember exacty why I didn't use @@IDENTITY. I do know there were a
couple
> of issues that I was dealing with. One of them I believe was making sure
> there was no way a user could insert a record (Mouse wheel, page down,
etc.)
> other than using the button on the form.

Another thing to be aware of with @@IDENTITY is that if you have triggers
that perform automagic inserts into other tables that in turn have IDENTITY
columns, you MUST contrive to cache @@IDENTITY coming into your trigger
(i.e. set @myid = @@IDENTITY)and reset it before leaving, or else access
will not be able to correctly track the row inserted and you will get error
messages (like, the row does not satisfy the underlying criteria, or some
such).

Here is snippet to reset @@IDENTITY to @myid before the trigger completes:

EXECUTE (N'SELECT Identity (Int, ' + Cast(@myid As Varchar(10)) + ',1) AS id
INTO #Tmp'

Cheers, 

-- 
Malcolm Cook - mec@stowers-institute.org
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO  USA
Quantcast