My objective is to get opinions on the method I use to get the most recently inserted ID. This ID is an auto genrated SQL Server integer primary key field.

SQL = "INSERT INTO...;"
conn.execute(sql)
sql = "SELECT TOP 1 ID FROM tbleName ORDER BY ID DESC;"
set getInfo = conn.execute(sql)

Is it possible for another insert to occur in between these two statements? Please indicate if your reply is from experience or best guess/asumption.

IMO: I believe it may be possible however I am yet to come accross someone who has actually seen it happen and I can not manage to make this occur (I have tryed)

Secondly does anybody have a suggestion for improvement? I do not wish to use an SP, I like my systems to be portable with Access with as little change as possible. I also dont mind the fact that I am executing two queries.

TYIA

Wind is your friend
Matt

__________________
Wind is your friend
Matt

mmmmmm, not even any opinions?

Wind is your friend
Matt

why not use @@identity? its supported in Jet 4 so u can use it for access and sql server

"Select @@IDENTITY"
will retrieve the latest inserted record.
use this query just after the execution of insert statement.

Thankyou for your replies. I was unaware of this syntax.

My question still remains:

"Is it possible for another insert to occur in between these two statements?" May I add weather using the method I use or this new 'Select @@IDENTITY' method

Wind is your friend
Matt

why not using the seesion id to make sure the id was last created by this session?

;;why not using the seesion id to make sure the id was last created by this session?

I dont understand what question you are advising on. Sorry, I cant see how it relats to my primary un-answered question.

FYI : am not looking for another way carry out a process, I am looking for advise on the one illustrated above and input on the primary question.

Wind is your friend
Matt

read this

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

Please next time try to search your question on google, you might find your answer.

You are interesing character. your orignal post said (got emailed):
-------------------start----------------------------
Okay,

I do not tried my self.

but if statment

sql = "SELECT TOP 1 ID FROM tbleName ORDER BY ID DESC;"

you may got the wrong id.

if statment

sql = "SELECT TOP 1 ID FROM tbleName WHERE SID='Session.SessionID'
ORDER BY ID DESC;"

That will not have any problem with it.


-------------------finish---------------------------

FYI NO IT WILL NEVER GET THE WRONG ID using my method, why do you think this? The ID field is an auto incrimenting integer (like I illustrated in the very first post)

My question was never how to get the most recently inserted ID it was asking if another insert could occur between the two statements. I do not wish to insult anybodys inteligence by repeating myself, its all clearly explained in my very first post. People seem to want to suggest different ways of getting this ID. Although I appreciate this, its clearly not what I am looking for.

Then you tell me to ask google. Have a look at how much time I spend here assisting others (probably you some time in the future) youve got a check....

;;;you might find your answer.
No I did not, thats why I posted it here. Can you find the answer? I think you have lost track of what this post is about and what the primary question is

Wind is your friend
Matt

You would better to asking the sql companies that question instant.

because it depends how the sql been written. would it processed which ever the request by time when request or it been processed request by each connection.

If it's proccesed request by the time when request, then it's possible to have another id.

If been processed request by each connection then you will have unique id.

so asking somewhere else.