• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1378
  • Last Modified:

Access Project/SQL Server - Insert trigger and Identity column problem

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_insertdefaultchild 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.
0
Roger Dias
Asked:
Roger Dias
  • 8
  • 8
  • 2
  • +2
2 Solutions
 
Alan WarrenCommented:
testparent where id = ?' without success.

sql server doesnt like '?' wildcard
use %

Alan

0
 
apolloisCommented:
Dodge70,

Take a look at the following article.  Maybe it will help:

Error Message "The Data Was Added to the Database But the Data Won't Be Displayed..." When You Add Records to a Subform
http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q287627

Best Regards,
>apollois<
0
 
Roger DiasAuthor Commented:
Re: alanwarren comment - According to Access Help, ResyncCommand Property only uses ? for parameter markers.  I tried % anyway and oddly enough the error message went away but the ID that is shown on the form is from the testchild record inserted by the trigger, not the testparent ID.

Re: apollois comment - The article confirms that on SQL Server 2000, a "Select @@IDENTITY" is done to verify the value of the Primary Key just added.  On SQL Server 7.0 this action is omitted, causing an identity value of 0 to return and producing an error message like I'm experiencing.  Since I'm using SQL Server 2000, my problem is that @@IDENTITY  returns the ID of the last record added by the Insert trigger (testchild record), not the testparent record.  It seems to be an oversight on the part of the designers of Access to not do a "Select IDENT_CURRENT(<UniqueTable>)" instead.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Roger DiasAuthor Commented:
OK, call me desperate.  I'm offering 1000 points to anyone who can figure out how to write INSERT triggers that insert records with Identity primary keys WITHOUT breaking Access.  Please see my lengthy initital question.
TIA.
0
 
Roger DiasAuthor Commented:
Sorry.  500 points appears to be the max.  Newbie mistake.
0
 
Alan WarrenCommented:
One of these transact sql functions may help:

SCOPE_IDENTITY
Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.


IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.

They are available in your stored procedure and triggers but you'll have to see if you can get to them from ADO

perhaps:
sql = "select IDENT_CURRENT('TableName') as [ID]"
set rs = cmd.Execute "select IDENT_CURRENT('TableName')"
debug.print rs.fields("ID").Value

Also your error indicating that the new record can't be displayed becasue it doesn't match the current criteria may be solvable by using a different type of CursorLocation and CursorType on your underlying recordset.
set rs = new ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CursorType = adOpenKeyset   ' (or adOpenDynamic)
rs.Open etc...
set Me.Recordset = rs

Is adOpenDynamic the same as Recordset Type = "Updatable Snapshot" in the form property sheet ?

Alan
0
 
Roger DiasAuthor Commented:
Thanks alanwarren for your replies.  I tried modifying the properties in the underlying recordset as per your example and came up with the same error message. Made no difference wether CursorType was adOpenKeyset or adOpenDynamic.  Here's the code I used in the On Open event of the form.  It's basically what you had:

Private Sub Form_Open(Cancel As Integer)
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set conn = CurrentProject.AccessConnection
    Set rs = New ADODB.Recordset
    Set rs.ActiveConnection = conn
    rs.Source = "select * from testparent"
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenDynamic
    rs.LockType = adLockOptimistic
    rs.Open
    Set Me.Recordset = rs
    Set rs = Nothing
    Set conn = Nothing

End Sub

As you mentioned, ADO's behaviour in resyncing records does not appear to be accessible to tell it to use IDENT_CURRENT('testparent') instead of @@IDENTITY to retrieve the database-generated, auto-incrementing identity value for the newly inserted record.

Here's some exerpts from "Programming ADO" by David Sceppa (http://www.microsoft.com/mspress/books/sampchap/3445a.asp):

<EXCERPT>

The database uses this (insert) query to insert the record and informs the ADO Cursor Engine that the insertion was successful. Then the Cursor Engine issues the following query:

SELECT @@IDENTITY

The ADO Cursor Engine uses this query to retrieve the database-generated, auto-incrementing identity value for the newly inserted record and places that information into the appropriate field in the client-side Recordset object.

The SELECT @@IDENTITY query returns the last identity value generated on the connection, but the value might not be what you expect. If the INSERT command updates the record and then fires a trigger that inserts another record in a different table that includes an auto-incrementing identity field, you'll retrieve the value from this second table rather than the value of the identity field in the record you actually inserted.

</EXCERPT>

This is just stated as fact and not as an oversight or a bug.  The strange thing is that Access does not have this problem in an mdb with and ODBC link to SQL Server tables.  It appears to return the correct IDENTITY key.  I would have submitted this issue to Microsoft Support but my experiences in the past have not been very positive and I was hoping that I was just missing something.
0
 
apolloisCommented:
Dodge70,

How about if you modify the trigger code to get the IDENTITY from the new Insert:

@ID = SELECT @@IDENTITY

then the remainder of your trigger code.

and finally:

SELECT @ID AS @@IDENTITY

If this doesn't work, then have you considered writing a stored procedure to accomplish what the trigger does and return the @@IDENTIFY of the insert record.

Best Regards,
>apollois<
0
 
Roger DiasAuthor Commented:
Thanks for the suggestions apollois

@@IDENTITY is read-only so 'SELECT @ID AS @@IDENTITY' is rejected.  Even hard coding a return value such as 'SELECT 14 AS ID' has no effect on Access's error message.

Therin lies the problem.  How do you "return" the @@IDENTITY of the insert record (testparent)?  There does not appear to be a way to communicate anything from a trigger back to Access other than a RAISERROR.  For instance you can't do a "Return <#>" in a trigger.  Generating output in a trigger such as "SELECT <something>" is generally discouraged and doesn't work in my case anyway.

I've tried encapsulating the "insert into testchild" statement in the trigger into a stored procedure and calling it from the trigger but this still overwrites @@IDENTITY because it's still running in the same session as the trigger.
0
 
Alan WarrenCommented:
I thought that Scope_identity would work.

SCOPE_IDENTITY
Returns the last IDENTITY value inserted into an IDENTITY column in the same scope.

Good resource:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01d4.asp

Excerpt:
Enter SQL Server 2000. This not-so-little annoyance has been overcome with the introduction of the SCOPE_IDENTITY() function, which takes no parameters and returns to you the IDENTITY value of the last inserted row within the current scope. (When a trigger is fired, the scope changes from that of the statement that fired the trigger to the scope of the trigger itself. Once the trigger has completed, the scope is returned.) Now, if you call SCOPE_IDENTITY()—not @@IDENTITY—you'll get the value arising from the INSERT you execute, not that of the underlying trigger.



Alan

0
 
Roger DiasAuthor Commented:
alanwarren,

My problem is that I am unable to send this value back to Access.  Access (through ADO) always does a "SELECT @@IDENTITY" after a record is inserted.  I tried playing around with the form's Resync Command property but the only paramatet it accepts (according to on-line help) is "?" and this is always set to @@IDENTITY. There appears to be no way to tell it to use SCOPY_IDENTITY or IDENT_CURRENT instead.

So far all the articles I've read (including the one you listed), demonstrate the use of these new functions by inserting a record in Transact-SQL.  What about inserting a record from a data bound Access form?

I tried putting SELECT and/or RETURN statements at the end of the insert trigger but Access ignores them. Examples:
   Select SCOPY_IDENTITY()
   Return SCOPY_IDENTITY()
   Select IDENT_CURRENT('testparent')
   Return IDENT_CURRENT('testparent')

And as there appears to be no way to set the value of @@IDENTITY in a trigger.
0
 
nexusnationCommented:
Hi Dodge70,
This question has been abandoned and needs to be finalized (95 days since last comment).

   You can accept an answer, split the points, or get a refund. Go to
   http://www.cityofangels.com/Experts/Closing.htm for information and options.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access
0
 
Alan WarrenCommented:
Create an output parameter to return scope_Identity, the interrogate the ado command to expose in VB.

the following returns an output parameter called @LoanID

CREATE procedure tp_tbl_Loans_Insert (
  @LoanID                        int OUTPUT,
  @ContractNumber                nvarchar(12),
)
as

set nocount on
declare @err int
set @err = 0

begin
  insert into tbl_Loans (
    ContractNumber,
  )
  values (
    @ContractNumber,
  )
  set @err = @@error
  if @err <> 0 goto ExitProcedure
end

begin
  set @LoanID = scope_identity()
  set @err = @@error
  if @err <> 0 goto ExitProcedure
end

ExitProcedure:
return @err


GO



ADO Command eg..


  Set cmd = New ADODB.Command
  With cmd
    .CommandText = "tp_tbl_Loans_Insert "
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@LoanId", adInteger, adParamOutput, 4, LoanId)
    .Parameters.Append .CreateParameter("@ContractNumber", adVarChar, adParamInPut, 12, Contract)
    Set .ActiveConnection = CurrentProject.Connection
    .Execute , , adExecuteNoRecords
    Contract = .Parameters.Item("@LoanID")
  End With
0
 
1WilliamCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Delete question, no refund
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

1William
EE Cleanup Volunteer
0
 
Alan WarrenCommented:
Dodge70

Did my last post not solve your problem?
I set parameter ("@LoanID") to Scope_Identity()
and returned to access via ADO and assigned the return value to an Access variable called Contract

I hope it worked because if it didn't, I'm assigning contracts to the wrong customers.
Tell me I havn't got a problem here, please.


Regards Alan
0
 
Alan WarrenCommented:
Dodge70

Did my last post not solve your problem?

I set variable set @LoanID = scope_identity()
returned @LoanID to Access Procedure and assigned to Local variable "Contract" via ADO.

You have me worried now, am I issuing duplicate contract numbers?

Alan
0
 
Alan WarrenCommented:
Attention xxxyMod@EE,

Could you please leave this post open for a while longer, as I need to resolve some possible fundamental flaws in my application.

Regards Alan
0
 
Roger DiasAuthor Commented:
Attn: Alanwarren

Sorry for the delay in responding.  I wasn't getting notified in my inbox of your posts.

Your example you posted should work OK because you are using VB code run a stored procedure to insert a record into tbl_Loans and read an output parameter to get pack the key that was inserted.

The problem I described would arise if you had a simple Access form that was bound to tbl_Loans and you tried to add a record through it.  Try it and see.  If you had an insert trigger on tbl_Loans that inserts a record into another table (e.g. tbl_X) with an IDENTITY key field, then Access complains because ADO is returning the key of the tbl_X, not tbl_Loans.  You'd get the same problem if the form's Record Source is a stored procedure containing the statement 'select * from tbl_Loans'.  As in my previous comment,  there is no way to intercept this communication on an Access bound form.  
0
 
Alan WarrenCommented:
Dodge70

Thanks for your response mate.
Good luck with your app, sorry I couldn't be more helpfull.

Best regards Alan
0
 
Roger DiasAuthor Commented:
Attn: Moderator

Despite the best efforts of alanwarren and apollois, I believe ultimately the answer to my question is "you can't do that".  I worked around the problem by removing the offending trigger and rewriting it in ADO in my form.  Not as robust as a trigger since I have to reproduce this code wherever I insert records into test_parent table.

I am awarding 400 points to alanwarren and 100 points to apollois for their efforts.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 8
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now