Get SQL Identity via Access 2007
Hi all -
I have an Access 2007 front end, with a SQL Server 2000 back end.
I have a bound form to add new data to my table. The table uses a SQL Identity Seed for the primary key.
When the user clicks my Save button, I need to be able to save the record, retrieve the ID, and then use it to insert rows into some related tables.
The problem that I am having is that for a reason I do not know, the ID is not becoming available after the user saves the record.
Sample VBA:
I had previously been using Me.Dirty, but was told to try acCmdSaveRecord. It seems to be working slightly better, but still having issues getting that ID number.
If I step through the code, no problems at all. So I tried putting in DoEvents and a Refresh, but that didn't help.
How can I reliably get the ID number?
Side note: Eventually when I have time, I have plans to rebuild the form as non-bound, and creating a dynamic pass through query to do everything, that way I can use Identity_Scope(). But right now I just don't have the time. I need to put one more band-aid on this first.
Thanks!
I have an Access 2007 front end, with a SQL Server 2000 back end.
I have a bound form to add new data to my table. The table uses a SQL Identity Seed for the primary key.
When the user clicks my Save button, I need to be able to save the record, retrieve the ID, and then use it to insert rows into some related tables.
The problem that I am having is that for a reason I do not know, the ID is not becoming available after the user saves the record.
Sample VBA:
DoCmd.RunCommand acCmdSaveRecord
DoEvents
Me.Refresh
DoEvents
MyID = Nz(Me.txtMyID.Value, 0)
I had previously been using Me.Dirty, but was told to try acCmdSaveRecord. It seems to be working slightly better, but still having issues getting that ID number.
If I step through the code, no problems at all. So I tried putting in DoEvents and a Refresh, but that didn't help.
How can I reliably get the ID number?
Side note: Eventually when I have time, I have plans to rebuild the form as non-bound, and creating a dynamic pass through query to do everything, that way I can use Identity_Scope(). But right now I just don't have the time. I need to put one more band-aid on this first.
Thanks!
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
boag2000 - Thank you for your thoughts.
It's just a snippet sample of the code from my Save button. The problem is that after the record is saved, the ID field is supposed to populate with the newly generated identity. But it doesn't always.
After the snippet, I verify that the ID has been captured, and then proceed to run several insert statements that insert other data into a related table that requires the foreign key.
It's not that I allow for nulls, but rather in code further down, I check to make sure that the ID has been attained. If it has not been attained, I do not run the insert statements, nor do I close out of the form. I pretty much just exit out of the sub. The problem is though for another unknown reason, the recordset then proceeds to change to #DELETED... which is a mystery because the record is not actually deleted. It did in fact save.
capricorn1 - Thank you as well for your thoughts.
I'm not 100% sure this will work for me. From the website you linked to:
Unless I am misunderstanding (which is certainly possible), the connection for the bound form and the ADO connection to retrieve the @@Identity will be different, and therefore will not work.
On a related note, I also would not be able to use IDENT_CURRENT('tablename') because I could potentially have several users inputting data at the same time. I need to make sure to acquire the proper ID.
It's just a snippet sample of the code from my Save button. The problem is that after the record is saved, the ID field is supposed to populate with the newly generated identity. But it doesn't always.
After the snippet, I verify that the ID has been captured, and then proceed to run several insert statements that insert other data into a related table that requires the foreign key.
It's not that I allow for nulls, but rather in code further down, I check to make sure that the ID has been attained. If it has not been attained, I do not run the insert statements, nor do I close out of the form. I pretty much just exit out of the sub. The problem is though for another unknown reason, the recordset then proceeds to change to #DELETED... which is a mystery because the record is not actually deleted. It did in fact save.
capricorn1 - Thank you as well for your thoughts.
I'm not 100% sure this will work for me. From the website you linked to:
The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection. This last bit is important, because it means that the Connection object used for the Insert query must be re-used without closing it and opening it up again.
Unless I am misunderstanding (which is certainly possible), the connection for the bound form and the ADO connection to retrieve the @@Identity will be different, and therefore will not work.
On a related note, I also would not be able to use IDENT_CURRENT('tablename') because I could potentially have several users inputting data at the same time. I need to make sure to acquire the proper ID.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
OK, then it seems that you can select your own post as the solution here
Thank you for sharing.
;-)
JeffCoachman
Thank you for sharing.
;-)
JeffCoachman
ASKER
"Please state your reason for accepting your own comment as the solution. "
Found documents listing issue as known bug.
Awarding points to those who assisted, and had good thoughts.
Found documents listing issue as known bug.
Awarding points to those who assisted, and had good thoughts.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Dim MyID as long
DoCmd.RunCommand acCmdSaveRecord
MyID = Me.txtMyID
msgbox MyID