Inserting Record & Retrieving Key (in an Access DB)

[CJM]

How do I retrieve the key value when I INSERT a record into an Access DB
using a a Dynamic SQL statement (via ADO)?

In SQL Server, I would use a Stored Procedure and return SCOPE_IDENTITY..

However, this scenario is using dynamic SQL and an access db, so I'm
stumped...

Thanks

Chris

 

[Bob Barrows [MVP]]

How do I retrieve the key value when I INSERT a record into an Access
DB using a a Dynamic SQL statement (via ADO)?

In SQL Server, I would use a Stored Procedure and return
SCOPE_IDENTITY..
However, this scenario is using dynamic SQL and an access db, so I'm
stumped...

Assuming you're using the Jet 4.0 OLE DB Provider, you can use SELECT
@@IDENTITY immediately after doing your INSERT

http://www.aspfaq.com/show.asp?id=2174

Bob Barrows

 

[CJM]

Thanks Bob,

This is a temporary solution so it doesnt matter much, and it's unlikely
that @@identity will return the wrong ID (low-usage system), but obviously
it's not ideal.

On the other hand, I hate Aaron's suggested solution of using rs.insert.

Out of intersest, do you know if/how this could be achieved with MySQL?

Thanks

 

[Bob Barrows [MVP]]

Thanks Bob,

This is a temporary solution so it doesnt matter much, and it's
unlikely that @@identity will return the wrong ID (low-usage system),
but obviously it's not ideal.

Actually, it IS ideal. It is much better than using SELECT max(id). Even
with a high-usage system, it should be bulletproof. From
http://support.microsoft.com/?kbid=232144:

******************************************************************
SUMMARY
The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query
that allows you to retrieve the value of the auto-increment field generated
on your connection. Auto-increment values used on other connections to your
database do not affect the results of this specialized query. This feature
works with Jet 4.0 databases but not with older formats.
**********************************************************************

On the other hand, I hate Aaron's suggested solution of using
rs.insert.

I think he does too. He was simply trying to be complete. In earlier
versions of Access (pre-Jet 4.0), @@IDENTITY did not exist, so using a
server-side cursor was the only way to dependable achieve this objective

Out of intersest, do you know if/how this could be achieved with
MySQL?

Not a clue, and not really interested in finding out.
I assume MySQL documentation is freely available somewhere.

Bob Barrows

 

[CJM]

Actually, it IS ideal. It is much better than using SELECT max(id). Even
with a high-usage system, it should be bulletproof. From
http://support.microsoft.com/?kbid=232144:

******************************************************************
SUMMARY
The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query
that allows you to retrieve the value of the auto-increment field
generated on your connection. Auto-increment values used on other
connections to your database do not affect the results of this specialized
query. This feature works with Jet 4.0 databases but not with older
formats.
**********************************************************************

Re-reading it again, it does suggest that if I use the same connection I am
OK. In those conditions, it seams like @@identity behaves effectively like
Scope_Identy() in SQL Server. Initially, I thought that connection pooling
would leave me vulnerable, but obviously if I dont relinquish my connection,
I'm OK.

Not a clue, and not really interested in finding out.
I assume MySQL documentation is freely available somewhere.

Not a fan? This whole thing is for a personal project, and unfortunately I'm
too tight-fisted to pay for SQL Server hosting... so it's either Access or
MySQL.

Yes, I've got other places to go for MySQL info, so if I get stuck, I'll
post a question there..

Thanks for the help.

Chris