From what I know, SELECT @@IDENTITY, is a Transact SQL call that is a
function of SQL server. I think what is happening here, is Access is
making the call to the server and generating the call. Just an educated
guess, but how would the ODBC driver know to make this call after an
insert unless it kept track of the table schema for every table. Seems
like more of a higher level function, Access learns the schema for the
tables you have open, then makes the proper call. Of course being MS
Access working with MS SQL might have something to do with it.
Tha'ts one of the reasons I wound up using the function/stored procedure
solution. The ODBC driver knows how to handle functions, and my front
end doesn't have to know anything but how to put the proper pararmeters
in and read them on return, after making the proper call.
Again, I'm not an Access person, so I can't pretend to know how to do
this through Access tables. It does work great when programming in
Delphi though, or for that matter wrapping your SQL code in a higher
level language.
Philippe Lang wrote:
>Hello,
>
>Thanks for your answers.
>
>select currval('test_id_seq') works, I have tested it. But the problem is that what I have described is done
transparentlyby MS Access, when a line is added through a linked table, or through a subform. There is no opportunity
forme to call currval, except if I insert a line into the table programmatically, what I want to avoid.
>
>Is there maybe a way to incorporate a call to currval into the driver?
>
>I have traced what SQL Server 2000 does, and there is call
>
>SELECT @@IDENTITY
>
>just after the insert. Does that mean that the problematic SQL call I have just talked about before
>
>select id from test where code = 20
>
>is a driver feature, more than a MS Access feature? If someone has an idea for a driver patch...
>
>Philippe
>
>
>
>