instead of running an insert statement you could do this
rs.AddNew
ID = rs![ID]
...
rs.Update
Printable View
instead of running an insert statement you could do this
rs.AddNew
ID = rs![ID]
...
rs.Update
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
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
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
Quote:
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 - ?
??? Is it possible to get the ID immed after Addnew?Quote:
rs.AddNew
ID = rs![ID]
...
rs.Update
Reallly need this solved bcos currently I am doing a cheap Select max ? (no multi user prob for me however)
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
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
:)
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
It works with Jet4.0 and up, and a few others. Here's the info.
http://support.microsoft.com/default...;EN-US;q195910