AW: Updated: [dba-SQLServer] #Deleted-Problem (Cross-posted)

Michael Brösdorf michael.broesdorf at web.de
Fri Jul 2 10:30:23 CDT 2004


But that 'moves' the cursor to the first record.
I would need to find out how the user navigated out of the new record (using
the up/down-arrow-keys or by pressing enter etc.) Plus, I don't know the ID
of the new record so I don't even have a starting point??

Right now I am willing to go for an unbound form with a grid control (which
gives me better control of what is going on anyway...)

Looks like a loooong weekend of coding... :-(

Michael

-----Ursprüngliche Nachricht-----
Von: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]Im Auftrag von Arthur
Fuller
Gesendet: Freitag, 2. Juli 2004 17:05
An: dba-sqlserver at databaseadvisors.com
Betreff: RE: Updated: [dba-SQLServer] #Deleted-Problem (Cross-posted)


A simple fix for this problem is to requery the form after the insert.

HTH,
Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Michael
Brösdorf
Sent: Friday, July 02, 2004 9:25 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Updated: [dba-SQLServer] #Deleted-Problem (Cross-posted)


Using SQL Profiler I found that Access indeed uses 'SELECT @@IDENTITY'
to get the ID of the newly inserted record. Unfortunately this will
return the new ID-value from table B.

Using Scope_Identity could solve the problem. Is there a way to tell
Access or the ODBC-Driver to use Scope_Identity instead of @@INSERTED?

Michael

-----Ursprüngliche Nachricht-----
Von: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]Im Auftrag von
Michael Brösdorf
Gesendet: Freitag, 2. Juli 2004 14:50
An: dba-sqlserver at databaseadvisors.com
Betreff: [dba-SQLServer] #Deleted-Problem (Cross-posted)


Dear group,


I have a problem with my Access2000-App. The Backend is SQL Server 2000

A bound form is linked to Table A. Table A has an insert-trigger, that
creates a record in table B. Both tables have an ID-Field (A_ID and
B_ID).

Whenever I save a new record in the bound form, it either immediately
displays #Deleted# in every field, or displays an old record(!).
However, the new record is created correctly.

There's a knowledgebase article, describing that problem. It states,
that Access tries to re-select the
new record using the ID value. It looks like that it is using the
ID-value of table B instead that of table A to do the re-select. If it
finds a record with that ID it displays that in the new row on the bound
form. If it does not find it, the form displays #Deleted#.

Is there any way to tell the trigger on table A not to return the ID of
the new record in table B?

Michael

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list