does anyone know if there is an issue with using @@identity more than once in the same procedure? if so, is there a workaround? i am trying to insert information in one table, then get the autonumber value for use in a second table, then get the autonumber from the second table for use in other tables. getting the first autonumber works just fine, but the second one always gives me a key violation error and the variable that stores the second autonumber is showing a value of 0.
any help is appreciated!
does anyone know if there is an issue with using @@identity more than once in the same procedure? if so, is there a workaround? i am trying to insert information in one table, then get the autonumber value for use in a second table, then get the autonumber from the second table for use in other tables. getting the first autonumber works just fine, but the second one always gives me a key violation error and the variable that stores the second autonumber is showing a value of 0.
any help is appreciated!
It is going to be hard for people to tell you what is wrong without seeing what you are doing. Why don't you post your code, and point out which line is throwing the error?
basically what i'm trying to do is add a new medical practice to my database. i keep the locations in a separate table, so when i add a new practice i first check to see if the combination of city, state, and zip exists in my table and then add it if i need to. here is the code i am using for that (also, i'm relatively new to access so i realize i might not be doing this in the most efficient way):
Code:
'checks to see if the new practice location already exists in tblPracticeLocation and obtains the
'ID if it does or adds it to the table if it doesn't
If (DCount("City", "tblPracticeLocation", "[City] ='" & strCity & "' AND [State] = '" & strState & "' AND [ZIP] = '" & strZip & "'")) > 0 Then
Set rs = db.OpenRecordset("SELECT * FROM tblPracticeLocation WHERE City = '" & strCity & "' AND State = '" & strState & "' AND Zip = '" & strZip & "'")
lngLoc = rs!PracticeLocationID
rs.Close
Else
DoCmd.RunSQL "INSERT INTO tblPracticeLocation([City],[State],[Zip]) VALUES ('" & strCity & "','" & strState & "','" & strZip & "')"
Set rs = db.OpenRecordset("Select @@Identity as LastLocID;")
lngLoc = rs!LastLocID
rs.Close
End If
that part seems to work fine. but after that i add information to my main practices table and then try to get the autonumber id from that table and that's where everything goes wrong. here's the code for that:
Code:
'inserts the new practice information into tblPractices
DoCmd.RunSQL "INSERT INTO tblPractices([PracName],[PracAddress],[PracSuite],[PracSite],[PracManaFN],[PracManaLN]," & _
"[PracManaExt],[PracManaEmail],[PracticeLocationID]) VALUES (txtName.Value,txtStreet.Value,txtSuite.Value,txtWebsite.Value," & _
"txtContactFName.Value,txtContactLName.Value,txtContactPhone.Value,txtContactEmail.Value,'" & lngLoc & "');"
Set rs = db.OpenRecordset("Select @@Identity as LastID FROM tblPractices;")
lngID = rs!LastID
it will insert the values just fine and if i look into the table the autonumber has incremented correctly, but the code is returning 0 as the value of the id. i've looked over it quite a bit and i can't figure out why. not sure if i'm missing something simple or not. i would be grateful for any help!
i can probably use dmax in the short term while i'm the only user of the database, but i was hoping to find a way to get @@identity to work. in the future there will probably be multiple users and i've read that @@identity is the only way to get around issues that might arise if two users are using the database at the same time. i really doubt that will ever be a problem, but i want to make sure that i plan for it just in case. :/
thanks for the suggestion though! i will use that for myself at the moment so that i can actually make use of my form.
Personally, I'm inclined to avoid @@identity because it has the same problem as dmax(); it's not specific to your scope so there's a chance you may get a ID that someone else created, which isn't what you want.
I would also prefer to move everything into a single stored procedure that will perform the insertion and return a scope_identity(), or if you're running this off a form, use the form itself to add the record into the table which is far simpler and requires less code.
in the future there will probably be multiple users and i've read that @@identity is the only way to get around issues that might arise if two users are using the database at the same time.
i can probably use dmax in the short term while i'm the only user of the database, but i was hoping to find a way to get @@identity to work. in the future there will probably be multiple users and i've read that @@identity is the only way to get around issues that might arise if two users are using the database at the same time. i really doubt that will ever be a problem, but i want to make sure that i plan for it just in case. :/
thanks for the suggestion though! i will use that for myself at the moment so that i can actually make use of my form.
You might be right that there is a better solution, but I've used that method with no problem even with several people using the db at once. Realistically, 2 people would have to insert at precisely the same instant to run into a problem. If you do run into that problem, you could always do a dlookup on the autonumber instead of dmax, and just put a where clause to find the unique values you just inserted.
Edit: Or just follow Banana's advice. That is ALWAYS advisable.
thanks for the help all! i will try out banana's advice. i try to study up on coding myself but it can be hard to root out the different ways to accomplish something. all your suggestions are greatly appreciated.
A rare, fleeting visit (short on time these days but just trying to keep awake by spreading around...)
I'd have to disagree with some of what's been implied here.
(I'm not saying Banana's suggestion is bad as far as consolidation - though I'm not sure what you're consolidating. You're performing an insert, and then at a later stage you're performing another insert. Whether or not this is the same procedure makes no difference to the value retrieval).
If form based then yes I agree - the exposed Recordset (or more usefully one of its clones) makes it quite trivial... All standard recordset AddNew fare then.
The suggestion to use Scope_Identity() is a good one too IF you're using SQL Server and have a direct connection to that. Jet doesn't support the Scope_Identity() function. @@Identity was only introduced in Jet 4 (technically Jet's final version) and ACE hasn't changed that.
However what I'd disagree with is the disparagement you've received over using @@Identity.
It certainly is user specific (of sorts) i.e. other users adding values will not impact the value you have returned - it is the Autonumber value that you last inserted. But the issue to grasp is that it isn't User specific. ;-) It's connection specific.
This does guarantee that other users won't impact your data's validity (unless you were jumping through crazy VBA hoops to pool connections :-s) but it also means you can't mix technologies.
Access (the application) maintains its own connection to source databases.
DAO maintains another (assuming accessed through CurrentDb, which is supplied by Access VBA application object - obviously dedicated opened databases establish new separate connections).
ADO maintains yet another (assuming accessed through CurrentProject.Connection just as above).
Any method employed through one of these is outside the scope of the others.
Add a row using RunSQL (which is an Access application method) is outside the scope of the DAO and ADO connections.
[Note I've used the phrase "connection" slightly loosely here. It implies a connection object of the type ADO uses - but that's not what I'm refering to. These are internal Access maintained database links I'm talking about.]
If you want to read the inserted AN value then be consistent with your data access libraries.
i.e. For example using DAO throughout
With that consistency you'd see the results you expect.
Just to mention - the issue in SQL Server where we prefer Scope_Identity() over @@Identity isn't due to mutli-user issues, but same connection actions (the main culprit being triggers operating upon tables actioned, which in turn insert rows - which passes back the trigger inserted Identity value in @@Identity but doesn't do so using Scope_Identity().
(And yes, for any wondering - I believe @@Identity will continue just fine given data macro use in 2010 )
)
