This problem covers Access, SQL Server and ADO.
I'm developing a Project (.adp) in Access 2002 SP2 on a SQL Server 2000 SP2 database. Both the server and client have MDAC 2.7 (ADO 2.7) installed. I have two tables defined, both tables have an identity key column:
CREATE TABLE [dbo].[testparent] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (50)
CREATE TABLE [dbo].[testchild] (
[id] [int] IDENTITY (1000, 1) NOT NULL ,
[name] [char] (50)
There is an an INSERT trigger on the testparent table:
CREATE TRIGGER testparent_insertdefaultch
ild ON dbo.testparent FOR INSERT AS
set nocount on
INSERT INTO testchild(name) VALUES ('test')
I have a form where the Record Source is the testparent table and 2 bound fields, id and name. When you try to add a record through the form you get the following error message:
"The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source"
I tried setting the form's Unique Table property to testparent and the Resync Command property to "select * from testparent where id = ?' without success.
After some researching I discovered that ADO returns @@IDENTITY from SQL Server in order for Access to display the newly inserted record properly (see http://www.microsoft.com/mspress/books/sampchap/3445a.asp
). The problem is that @@IDENTITY gets reset to the key of the testchild record after the Insert statement in the trigger fires. This is what is used by the question mark in above mentioned Resync Command.
Inserting a testparent record through an insert statement in Query Analyser works no problem. Removing the Insert statement in the trigger causes the problem to go away but then that defeats the purpose.
My question is this. Is there any way to pass back to Access from SQL Server the identity value of testparent? What Access should really be using is SQL Server's IDENT_CURRENT('testparent'
) function but there appears to be no way to set @@IDENTITY to this in the trigger or communicate it back to Access so that it can resync properly. I have seen answers to questions similar to this on SQL Server forums but not a solution for bound Access forms on SQL Server.
The only workarounds I have come up with so far are:
1. Don't use identity fields or insert statements in triggers. Definitely last resort.
2. Set the form's Resync Command to "select * from testparent where id = 0" and refresh the recordset in After Update. This requires a placeholder record in the testparent table with id 0 and the extra overhead of repeated queries to the datebase and managing the bookmark to get the user back to the record he just added.
Both of these I consider to be a workaround and I'm hoping to finding a more elegant solution. Thanks in advance.