A
AG
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.
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.