>Access Front end, SQL Server back-end connected via ODBC

>We have a system of batch updates which are processed by by Access and
>updated,deleted or added to the server as needed.

>OK: Now the problem:

>We have a series of related tables: (simplified)

>[Address] 1 -> many [Person] 1->many [Subjects]

>On adding new records, I need to get the newly assigned ID (set by an
>identity column in SQL Server), so that the related records will also
>hold this same ID and therefore keep the data consistent.

>However, on inserting the record and then going back to it,

>....set fields in remote table to incoming values...
>rsRemote.Update
>rsRmote.MoveLast

>this creates an error (which took ages to find!) ...DAO thinks that
>the record has been deleted....

>What is the best way to go about getting this information back to the
>front-end?

>dirc Evans

>This also doesn't work!

>Don't ask me why but... rather than implement an identity column, the
>developer has written an ON INSERT  trigger that does exactly the
>same.. ie increments the ID number.

>I think that what is happening is that, since the record was changed
>immediately following the update, DAO thinks the the last record it
>holds is different to the one on the remote database.

>I have a few work arounds in mind that work in a single user
>situation, but would fall over if more than one record was being added
>at a time.

>Any ideas? I'm baffled!!!!

>>Try
>>rsRmote.Bookmark=rsRmote.LasModified
>>Best regards,