Return the Autonumber (resolved)

[NickP]

instead of running an insert statement you could do this

rs.AddNew
ID = rs![ID]
...
rs.Update

[MetallicaD]

true.. i would much rather do that.. but i need minimal code and need to utilize SQL as much as possible

any other ideas you have would be great!

thanks
-mcd

[ralph]

for jet4.0 oledb:
After the Insert sql is run, run the following strSQL somehow (with dataenv command or reular rs.open strsql ...):

strSQL = "SELECT @@Identity"
Will produce a one value recordset containing
NewAutonumber = rs(0).value

[MetallicaD]

That will work, but its not 100% perfect.. This is a multiuser system, and its very likely that people will be entering in data at the same time. There is nothing to say that another person wont enter a record at the same exact time.. in which I would get an inaccurate autonumber.

What I have decided to do was just retrieve the max(autonumbered field) where the employee who entered the record is the current employee.. This will take care of the multi-user problem

Thanks for your input!
-mcd

[hardcoder]

Originally posted by ralph
for jet4.0 oledb:
After the Insert sql is run, run the following strSQL somehow (with dataenv command or reular rs.open strsql ...):

strSQL = "SELECT @@Identity"
Will produce a one value recordset containing
NewAutonumber = rs(0).value

Does this work for Access? Access 97/2000?

I thot it only works for SQL Server.

And Does the following work too - ?

rs.AddNew
ID = rs![ID]
...
rs.Update

??? Is it possible to get the ID immed after Addnew?

Reallly need this solved bcos currently I am doing a cheap Select max ? (no multi user prob for me however)

[MetallicaD]

The .addnew way of doing it will work with access and sqlserver.. Using the @@Identity, and anyother variables, will not work with access since access doesnt store this information.

-mcd

[ralph]

Access has nothing to do with it. It's a front end to a jet database. Jet4.0 OLeDB is the issue. You want to argue with micro$oft?

http://support.microsoft.com/default...;en-us;Q232144

[MetallicaD]

WOW.. i stand corrected. I just tried this in my application and it works great! Will this work with other provider than the Jet4.0 OLeDB ?? Either way, you have just saved me some headaches and its time to go back to all the apps i have created in the past and modifiy some code!!

Thanks ralph, you are my new hero!
-mcd

[ralph]

It works with Jet4.0 and up, and a few others. Here's the info.
http://support.microsoft.com/default...;EN-US;q195910