Primary Key (Identity) SQL vs Access

[deadtrees via AccessMonster.com]

I just upsized my db from access to SQL server and I've encountered some
problems with my audit trail code. Turns out that for changes to existing
records, everything works fine. For creation of new records, there's a major
fail and I get a runtime error in the vba because I have the code set to run
in the beforeupdate event.

It's due to the fact that Access creates a new primary key when you first
enter data into a record, however SQL doesn't create an identity until after
you save the record.

Is there any way to change this behavior in SQL or is there a way I can move
my vba to trigger on a different event.

thanks in advance.

 

[Alex Dybenko]

hi,
best approach - if you move your audit trail VBA code to sql server triggers

Have a look at this tool - just to get an idea what you can do:
http://www.apexsql.com/sql_tools_audit.asp

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Sylvain Lafontaine]

You cannot change the behavior of SQL-Server and as on how to move your code
from the beforeupdate event to another such as the afterupdate event; I have
absolutely no idea.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bob McClellan]

I agree with alex that you would be better off using
sql triggers. ..that being said...

I just created a form using a table with a PK identity column.
I added code in the beforeupdate event. the code worked fine
when adding a new row. If you need the new ID number for whatever
you are trying to do .. and you need to do it on the vba side as opposed
to executing a stored procedure to modify the data,
....can you move the code to the afterupdate event?

IF you can use a stored proc to do the work you need done from the before
update... then
use scope_identity() to return the id when the new row is inserted and use
it where needed.

if you can elaborate a bit more on what you are actually doing, we might be
able to help a bit more.
hth,
...bob