Re: Can and Can't insert using DAO on SQL Server linked table

From: david epsom dot com dot au (david_at_epsomdotcomdotau)
Date: 08/28/03 

Date: Thu, 28 Aug 2003 09:11:22 +1000

Using JET 4, an append or insert that attempts to write a value
into the Identity field will set Identity Insert on. The insert
will succeed, and after that the autonumber won't work properly
until the Identity Insert is turned off again.

There is also a property that controls if JET tries to write
to all fields, or just to fields that have changed.

"Initialization Settings for Jet-connected ODBC Databases"
"PreparedInsert: An indicator of whether to use a prepared INSERT statement
that inserts data in all columns. Values are 0 (use a custom INSERT
statement that inserts only non-Null values) and 1 (use a prepared INSERT
statement). The default is 0 (values are of type REG_DWORD).Using prepared
INSERT statements can cause Nulls to overwrite server defaults and can cause
triggers to execute on columns that weren't inserted explicitly."

I suspect that you are writing a 0 to the Identity field: after that
it is not autonumber until Identity Insert is turned off again, and
you get an error the second time because you are writing another 0
(duplicate index) to the identity field. I don't know if you are
writing the 0 because of (a) coding error (b) JET bug (c) Non-default
value of PreparedInsert or (d) other.

You can use a pass-through query to reset the Identity Insert Property
"set identity_insert DBO.tblYC_YieldCurve off;"

Also, you may find that inserting into a different table may reset
this property: or you may find that inserting into a table may
prevent identity insert on other tables.

I am not aware of any documentation of this behaviour.
I am not aware of any documentation of the change between Jet 3 and 4.
I don't know if this varies between different releases of Jet 4.

If you find any documentation, or reach any conclusions, please let me know!

(david)

"Mark A. Sam" <msam@Plan-It-Earth.Net> wrote in message
news:%233M3MPEbDHA.3248@tk2msftngp13.phx.gbl...
> David,
>
> > 1) Which Version? A97/Jet 3.5? A2K/Jet 4? (they handle this
differently)
>
> 4.0
>
> > 2) If Identity Insert is on for the table, it won't generate AutoNumber
> ID's
>
> I don't understand what you mean. How do I check that property? Identity
> is set to Yes for the Primary Key which is autoincrementing.
>
> > 3) If you Insert an Identity value, it will turn Identity Insert on, and
> > it won't generate autonumber ID's until you turn it off again.
>
> I guess I need to know how to Turn Indentity Insert to Off.
>
> God Bless,
>
> Mark
>
>