?
Solved

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

Posted on 2003-03-06
20
Medium Priority
?
1,370 Views
Last Modified: 2012-06-27
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
Comment
Question by:Roger Dias
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 2
  • +2
20 Comments
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8085921
testparent where id = ?' without success.

sql server doesnt like '?' wildcard
use %

Alan

0
 
LVL 10

Expert Comment

by:apollois
ID: 8086472
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
 

Author Comment

by:Roger Dias
ID: 8089985
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Roger Dias
ID: 8137955
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
 

Author Comment

by:Roger Dias
ID: 8137959
Sorry.  500 points appears to be the max.  Newbie mistake.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8140443
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
 

Author Comment

by:Roger Dias
ID: 8155343
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
 
LVL 10

Assisted Solution

by:apollois
apollois earned 200 total points
ID: 8155432
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
 

Author Comment

by:Roger Dias
ID: 8160829
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8171245
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
 

Author Comment

by:Roger Dias
ID: 8175311
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
 
LVL 12

Expert Comment

by:nexusnation
ID: 8785319
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
 
LVL 26

Accepted Solution

by:
Alan Warren earned 800 total points
ID: 8785788
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
 
LVL 18

Expert Comment

by:1William
ID: 8884804
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8890920
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8890948
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8890961
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
 

Author Comment

by:Roger Dias
ID: 8895290
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8897904
Dodge70

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

Best regards Alan
0
 

Author Comment

by:Roger Dias
ID: 8904049
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question