SQL Server record key

[croydon]

I am working on an Access 2003 application where the data has been upsized to SQL Server 2005.

I need to make a change to a form's processing when a new record is added. This requires inserting a record in a second table using the same key.

The key for the original table is identity (autonumber). The problem is that I cannot determine the key of the new record added. I assume this may be related to the fact that SQL Server allocates the key after the record is created.

I have searched various forums and cannot find an answer. I understand that @@Identity returns the key of the last record added. This is of no use as it is a multi-user application.

Any suggestions would be appreciated.

 

[Uncle Gizmo]

This is quite a regular sort of question, I have seen it before! I would keep searching I'm sure there is an answer, although you appear to already have the answer that I recall, that it is not possible because the record number is updated last.

If it's a multiuser environment, maybe you should consider storing the source of the update, then you would be able to find the last record Number relating to the source.

 

[Pat Hartman]

Where is your code now? If it is in the click event of a button, just save the current record -

DoCmd.RunCommand acCmdSaveRecord

Then, as long as you have included the identity column in your query, you will be able to reference the autonumber assigned to the current record directly -

Msgbox "this is the new key " & Me.YourAutonumber,vbOKOnly

 

[croydon]

My understanding is that the record-id should be available after the record has been saved in Access or the user moves to a different record.

I have been playing with this function over the weekend and sometimes it has been working as expected, and other times (worryingly) it has not.

I have a text field and have set the LostFocus event to perform a Save (using DoCmd). Originally this worked ok but after adding some other controls (that do nothing special), when LostFocus-Save is run the record shows #Deleted in every field. Paging through the records in the subform, the new record cannot be found, but if the function is closed and reopened, the record is present. I can only assume the link between the main form and subform record has been lost.

I don't know whether this problem is linked to the way SQL Server processes with Access or whether it is a problem/corruption elsewhere in the system.

 

[Pavel69]

1) If you working with ADO (Access project), new record will be current after save.

2) If you working with DAO, new record can be access as bookmark via LastModified recordset properties

3) @@Identity is related to session (i.e. connection) so you can use it freely in multiuser enviroment. See @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT in SQL Books Online