SQL insert command, how to return the created id value?

[IBM]

I'm inserting into an access db, and I just want to return the id value of the row that's created. I used to be able to do it with Stored Procedures in MSSQL Server, but I'm not having much luck with straight SQL.

So I've got INSERT INTO tblName (fieldname1, fieldname2, fieldname3) VALUES ('a','b','c'), and I want it to return the id, so the next page in the site knows what entry I'm talking about. I don't want to get just the most recently created row, as I'm expecting fairly high traffic, and there's always the theoretical chance that someone will create a new item in the short period between insertion and retrieval.

I could always create a unique identifier from the server, date/time stamp, and then use that to retrieve the information, but that just seems daft, and there has to be an easier way to do it.

Anyone got any ideas?

Muchas Gracias, Merci, Terima Kasih, Ta, Danke, Much Obliged, and Thanks.

[TiG]

If you are expecting fairly high traffic what the hell are you doing using Access?. Surely MSDE would be so much more appropriate. Then you could use Stored Procedures and do this properly.

I'd certainly not advise doing a high load site against access.

TiG

[Spunkey]

as you mentioned 'next page' i assume your going to be calling this Stored Procedure from a PHP/ASP page?

If you're using ASP, either of these should do it...

set rsTest = server.createobject("ADODB.RecordSet")
rsTest.Open "spTestStoredProc", Conn, 3, 1    ' the 3,1 cursor and locktype enable the recordset to return the primary key.
  iNewID = rsTest("ID")
rsTest.close
set rsTest = nothing

some server installations have a beef with that, so if that dont not work, try this...

set Conn = server.createobject("ADODB.Connection")
SQL = "INSERT INTO Test (Column1,Column2) VALUES ('Norks','Baps');
cn.execute SQL

set rsTestID = server.createobject("ADODB.Recordset")
rsTestID.open "SELECT @@IDENTITY AS 'NewID';", Conn, 2, 3
  iNewID = rsTestID("NewID")
rsTestID.close
set rsTestID = nothing

HTH!

*edit*
and what TiG said, Access Backend + High Volume Site = Tears and P45

[IBM]

It's a fair cop guv, and you're quite right, I should indeed be avoiding Access like the plague infested totally inadequate thing it is.

However, I haven't worked with SQL server in three years, and then it was within a company with legit copies, and a support service to help me through the numerous problems I've encountered, plus a load of in house talent to assist my learning. If it turns out that the site generates the kind of traffic I'm talking about, then I'll be able to pay a SQL bod to port all my stuff across to SQL server. Until then I'm stuck with Access, although since you're quite right, I'll start digging through my old SQL Server resources and seeing where I can get to....although I still can't afford a legit copy of Server for development, and I'm not one for supporting software piracy....

So with that in mind, does anyone have any helpful suggestions to the problem at hand, and at least that way, even if I do decide to scrap the access stuff done so far, I'll know my code is solid?