Re: Access - ODBC - serial problem...

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 transparently by MS Access, when a line is added 
> through a linked table, or through a subform. There is no opportunity for me 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
>
>
>  



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faqs/FAQ.html