Identity Column in SQL Server View

Access 2007 front end with SQL Server 2005 Express back end.

Using SQL Server Profiler, I have observed the following:

When an Access datasheet form is bound to a table (or view containing one
table), with an Identity (Access Autonumber) column, and a new record is
inserted via the bound form, Access uses sp_executesql to insert the new
values into the table (or view).
If I look at design view of the table (or view) in Access, the identity
column appears as an autonumber.
Since Access recognizes the Identity (Autonumber) column, it uses SELECT
@@Identity to retrieve the new identity value.

In my application, I have a datasheet form bound to an view.
The view contains several tables and does have a unique clustered index and
does contain an identity column.
However, when I look at design view in Access, the PK(identity) column does
not appear as an autonumber.

The view uses INSTEAD OF triggers to handle inserts, updates and deleted.
The only problem I have found is on inserting.

As with a bound table, Access inserts the record using sp_executesql, and
the new record(s) is inserted.
However, Access then needs to retrieve the new record.
Since it does not recognize an identity column, it uses sp_executesql to
retrieve the new record, using the values of all of the inserted columns as
parameters.
Only columns where values were entered (or changed) are included. This works
as long as the user only enters values and does not change their mind.

If a value is entered into a column that allows nulls and then the value is
removed to leave the column empty (before the record is saved), when Access
requests the new record with sp_executesql, it includes a parameter for the
nullable field and specifies NULL for the value of the parameter.

A select statement executed with sp_executesql and a parameter value of
NULL, returns no records.
Therefore, Access displays #DELETED, instead of the new record.
Obviously, this is not acceptable.

Note, the new record does exist in the appropriate table(s).

If nothing is ever entered into the nullable column, or a value is retained,
the new record appears fine.

Is there any way to specify an identity(autonumber) column for the view, so
that Access would recognize it, and not query by the entered values?

Any other suggestions?

A requery of the form is not an option.

--

AG
Email: npATadhdataDOTcom

Maybe http://support.microsoft.com/kb/q209123/ .

--
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)


"AG" wrote in message
...
Access 2007 front end with SQL Server 2005 Express back end.

Using SQL Server Profiler, I have observed the following:

When an Access datasheet form is bound to a table (or view containing one
table), with an Identity (Access Autonumber) column, and a new record is
inserted via the bound form, Access uses sp_executesql to insert the new
values into the table (or view).
If I look at design view of the table (or view) in Access, the identity
column appears as an autonumber.
Since Access recognizes the Identity (Autonumber) column, it uses SELECT
@@Identity to retrieve the new identity value.

In my application, I have a datasheet form bound to an view.
The view contains several tables and does have a unique clustered index
and does contain an identity column.
However, when I look at design view in Access, the PK(identity) column
does not appear as an autonumber.

The view uses INSTEAD OF triggers to handle inserts, updates and deleted.
The only problem I have found is on inserting.

As with a bound table, Access inserts the record using sp_executesql, and
the new record(s) is inserted.
However, Access then needs to retrieve the new record.
Since it does not recognize an identity column, it uses sp_executesql to
retrieve the new record, using the values of all of the inserted columns
as parameters.
Only columns where values were entered (or changed) are included. This
works as long as the user only enters values and does not change their
mind.

If a value is entered into a column that allows nulls and then the value
is removed to leave the column empty (before the record is saved), when
Access requests the new record with sp_executesql, it includes a parameter
for the nullable field and specifies NULL for the value of the parameter.

A select statement executed with sp_executesql and a parameter value of
NULL, returns no records.
Therefore, Access displays #DELETED, instead of the new record.
Obviously, this is not acceptable.

Note, the new record does exist in the appropriate table(s).

If nothing is ever entered into the nullable column, or a value is
retained, the new record appears fine.

Is there any way to specify an identity(autonumber) column for the view,
so that Access would recognize it, and not query by the entered values?

Any other suggestions?

A requery of the form is not an option.

--

AG
Email: npATadhdataDOTcom

ok
"AG" a écrit dans le message de
...
Access 2007 front end with SQL Server 2005 Express back end.

Using SQL Server Profiler, I have observed the following:

When an Access datasheet form is bound to a table (or view containing one
table), with an Identity (Access Autonumber) column, and a new record is
inserted via the bound form, Access uses sp_executesql to insert the new
values into the table (or view).
If I look at design view of the table (or view) in Access, the identity
column appears as an autonumber.
Since Access recognizes the Identity (Autonumber) column, it uses SELECT
@@Identity to retrieve the new identity value.

In my application, I have a datasheet form bound to an view.
The view contains several tables and does have a unique clustered index
and does contain an identity column.
However, when I look at design view in Access, the PK(identity) column
does not appear as an autonumber.

The view uses INSTEAD OF triggers to handle inserts, updates and deleted.
The only problem I have found is on inserting.

As with a bound table, Access inserts the record using sp_executesql, and
the new record(s) is inserted.
However, Access then needs to retrieve the new record.
Since it does not recognize an identity column, it uses sp_executesql to
retrieve the new record, using the values of all of the inserted columns
as parameters.
Only columns where values were entered (or changed) are included. This
works as long as the user only enters values and does not change their
mind.

If a value is entered into a column that allows nulls and then the value
is removed to leave the column empty (before the record is saved), when
Access requests the new record with sp_executesql, it includes a parameter
for the nullable field and specifies NULL for the value of the parameter.

A select statement executed with sp_executesql and a parameter value of
NULL, returns no records.
Therefore, Access displays #DELETED, instead of the new record.
Obviously, this is not acceptable.

Note, the new record does exist in the appropriate table(s).

If nothing is ever entered into the nullable column, or a value is
retained, the new record appears fine.

Is there any way to specify an identity(autonumber) column for the view,
so that Access would recognize it, and not query by the entered values?

Any other suggestions?

A requery of the form is not an option.

--

AG
Email: npATadhdataDOTcom

Thanks, Sylvain.
My view already has a PK. It is not a question of it being updatable, just
how Access retrieves the inserted record from SQL Server.

--

AG
Email: npATadhdataDOTcom


"Sylvain Lafontaine" wrote in message
...
Maybe http://support.microsoft.com/kb/q209123/ .

--
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)


"AG" wrote in message
...
Access 2007 front end with SQL Server 2005 Express back end.

Using SQL Server Profiler, I have observed the following:

When an Access datasheet form is bound to a table (or view containing one
table), with an Identity (Access Autonumber) column, and a new record is
inserted via the bound form, Access uses sp_executesql to insert the new
values into the table (or view).
If I look at design view of the table (or view) in Access, the identity
column appears as an autonumber.
Since Access recognizes the Identity (Autonumber) column, it uses SELECT
@@Identity to retrieve the new identity value.

In my application, I have a datasheet form bound to an view.
The view contains several tables and does have a unique clustered index
and does contain an identity column.
However, when I look at design view in Access, the PK(identity) column
does not appear as an autonumber.

The view uses INSTEAD OF triggers to handle inserts, updates and deleted.
The only problem I have found is on inserting.

As with a bound table, Access inserts the record using sp_executesql, and
the new record(s) is inserted.
However, Access then needs to retrieve the new record.
Since it does not recognize an identity column, it uses sp_executesql to
retrieve the new record, using the values of all of the inserted columns
as parameters.
Only columns where values were entered (or changed) are included. This
works as long as the user only enters values and does not change their
mind.

If a value is entered into a column that allows nulls and then the value
is removed to leave the column empty (before the record is saved), when
Access requests the new record with sp_executesql, it includes a
parameter for the nullable field and specifies NULL for the value of the
parameter.

A select statement executed with sp_executesql and a parameter value of
NULL, returns no records.
Therefore, Access displays #DELETED, instead of the new record.
Obviously, this is not acceptable.

Note, the new record does exist in the appropriate table(s).

If nothing is ever entered into the nullable column, or a value is
retained, the new record appears fine.

Is there any way to specify an identity(autonumber) column for the view,
so that Access would recognize it, and not query by the entered values?

Any other suggestions?

A requery of the form is not an option.

--

AG
Email: npATadhdataDOTcom