Full Version: getting # from SQL Indentity Field
UtterAccess Forums > Microsoft® Access > Access Forms
AmmoAndy
I need to return the indentity # from an SQL table (fieldname ID) for a form that I opened in MS ACCESS 2007 for data entry. Here is the code I have:
oCmd.RunCommand acCmdSaveRecord
Me.AssessmentID = Format(Date, "yyyy") + "-" + Trim(Str(Me.ID)) ' assign the year and a - to the # from the ID field
DoCmd.RunCommand acCmdSaveRecord
The above works just fine, but I was trying to use the:
CurrentDb.OpenRecordset("select @@Identity from tblAssessment")(0)
but this always returns a 0
Any idea of what I am doing wrong? Can anyone see a problem with the first statement?
Thanks
bulsatar
Why the double @'s? Unless that is the beginning of the field name (which if it is, you should change to something more....appealing to Access). If you want to give it an Alias, you would use something like this:
urrentDb().OpenRecordSet("SELECT [fieldname ID] As Identity FROM tblAssessment",dbOpenDynaset)
or, from what you have listed below:
CurrentDb().OpenRecordSet("SELECT [fieldname ID] FROM tblAssessment",0)
Hope that helps <
dannyseager
There should be no need to do any of this... as you seem to be using a bound form you should be able to just use
e.YourIDField
This assumes you've added the identity field to your form as a control
dannyseager
Bulsatar
@Identity is how you retrieve the identity of the last record inserted in a table... but for SQL server I would recomend Scope_Identity() instead : @@Identity vs Scope_Identity()
LPurvis
Hi
It's the means by which you execute the insert and then request the Identity.
Here's a couple of links to begin with.
Connection specific nature:
http://www.utteraccess.com/forum/t1840480.html
Want a nice bit of debate about using it at all? (I'm right of course lol ;-p)
http://www.utteraccess.com/forum/t1899306.html
Having saved the record through a form, you've used Access' own application connection to the database.
This differs from all code object connections (even that persisted via CurrentDb).
@@Identity is connection specific (as discussed in link one) so if you're saving through the UI you have very little chance of performing an @@Identity request (and even less of usefully reading it).
However forcing the record to save would result in the Identity value to be inserted from the server, at which point you can read the value.
After your save line, have you tried just reading the Identiy value from your form?
("Me.Dirty = False" is often preferred as less likely to fail).
To perform a read of the new ID in code, you'd have had to insert it, not only from ccode - but from an object maintaining the same connection to the database.
(CurrentDb does do that. ADO objects are more transient)
Cheers.
LPurvis
I was busy link hunting while Danny was posting lol.
would point out that Scope_Identity(), though generally preferable yes, won't be available through any local objects.
(That would include queries performed from CurrentDb and CurrentProject.Connection. Only PT's or an ADO connection directly to the server).
Unless this is an ADP of course - in which case the latter is fine and the former would fail completely (and as you're using it in your example code - then it's reasonable to assume this isn't an ADP).
Cheers.
bulsatar
Lol, everyday here I learn how much at the beginning of all of this I really am!
AmmoAndy
Like I stated, the first part works fine, as long as I save the save the record first. From what I am gathering on SQL server ( haven't used it before) is, unlike access when you start typing into a bound data entry form the identity field is not populate until the record is saved. So does the @@Identity will only return the value when a record is saved or inserted, there is no way of returing the ID from a "dirty" record, that is added, unitil it is saved or insert is accomplished?
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.