Treba da znamo st ti je back end baza. razlicito se radi ako je back end MDB i ako je back end MS SQL.
Ako je back end MDB - Access baza, onda ne mozes kosrititi @@IDENTITY - takva funkcija ne postoji u Accessu. Ti koristis ADO. ADO mora da ima neki metod koji moze da prepozna koji je poslednji rekord koji si upravo dodao, pa onda procitas polje koje te zanima. Da koristis DAO, rekao bih ti da koristis LastUpdated (ili tako nekako) property/metod za rekordset. Takodje bi morao umesto DoCmd.OpenQuery "qryInsertData" da odradis INSERT kroz DAO.AddNew - DAO.Update metode. Pogledaj help za ADO, verovatno postoji nesto sto ti moze dati odgovor na pitanje 'koji sam poslednji rekord insertovao"
Ako je back end MS SQL, opet ne ide @@IDENTITY. I opet ne moze DoCmd.OpenQuery "qryInsertData"
Trebao bi da napravis stored procedure koja radi nesto kao:
BEGIN
INSERT INTO myTable Field1, field2,... VALUES (Value1, Value2...)
RETURN SCOPE_IDENTITY()
END
Onda tu proceduru pozoves iz Accessa ili iz neke druge procedure. Komplikovano? Jeste. Kako bi bilo manje komplikovano? Predji sa autonumber/identity na prirodni Primary Key. Onda ces morati da izracunas vrednost PK pre nego se odlucis na INSERT INTO.
Izvod iz MS SQL help-a:
Citat:
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.
Syntax
SCOPE_IDENTITY( )
Return Types
sql_variant
Remarks
SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.
SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.
For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.
Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.
@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.
SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.
See Examples for an illustration.