Indentity_Insert

[david epsom dot com dot au]

I am currently converting an A97/SQL Server application to A2K/SQL Server.

Has anyone seen any documentation that deals with Identity Insert in A2K?

Correct me if I am wrong:

I find that A2K allows you to Insert an Identity on linked SQL Server
tables, just like on Jet tables.

I think that this is different from A97 on the same PC.

You may only set Identity_Insert on one table at a time.

When using A97, you must use pass-through queries to turn Identity_Insert ON
buffer appending to an Identity field, and OFF again afterwards (so that you
can set Identity_Insert on another table later).

When using A2K you don't have to worry about using 'Set Identity_Insert On',
or 'Set Identity_Insert Off'.

But if you do an insert that appends to the Identity column, you can't then
do an insert that does NOT append to the identity column: because the Access
append query silently does 'Set Identity_Insert On' -- and leaves it turned
on, so that a latter append fails if it does not provide an identity value
to insert.

But Identity_Insert may only be turned on for one table at a time, so Access
must issue some other instruction to turn it off before attempting an
identity insert on another table?

And the A2K Identity Insert only works with simple Insert queries? Because
if you do an implicit append using an update query, it doesn't know to set
Identity_Insert On ???

any response welcome
(david)