Returning Identity value to Access97...

[Ajb2528]

I have an Access97 front-end linked to a SQL Server 7 Database. After inserting a row within a table (that has an Identity field) I need to return to the front-end the value of the Identity field. The current version of the front-end uses linked tables thru ODBC and therefor (I think) uses DAO for all database access.

A typical insert uses the following...

DoCmd.GoToRecord , , acNewRec

If I convert this to a SQL insert (using DAO) can I return to the Access front-end the value of the Identity field???

Any help would be appreciated...

Regards,

Alan

 

[GHolden]

Hi,

The only way I know to do this is to have a SQL Server stored procedure to do the Insert that returns the Identity value (using @@Identity).
You would need to call this from Access using an ADO connection.



There are two ways to write error-free programs; only the third one works.