Solved

Get SQL Identity via Access 2007

Posted on 2013-01-07
7
486 Views
Last Modified: 2013-01-19
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:
        DoCmd.RunCommand acCmdSaveRecord
        DoEvents
        Me.Refresh
        DoEvents
        MyID = Nz(Me.txtMyID.Value, 0)

Open in new window


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!
0
Comment
Question by:Castaway78
  • 3
  • 3
7 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
ID: 38753157
Is that the entire code?
What event is this on?
Why are you allowing for nulls?
You don't really need to use the .value property, it is presumed by default.

I am no expert on SQL Identity fields, but what happens if you just do this on your save button?

Dim MyID  as long
    DoCmd.RunCommand acCmdSaveRecord
    MyID = Me.txtMyID
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38753176
Then do this to be sure:

Dim MyID  as long
    DoCmd.RunCommand acCmdSaveRecord
    MyID = Me.txtMyID
    msgbox MyID
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
ID: 38753182
use ADO recordset

Dim rst As ADODB.Recordset
Dim lngID As Long

< Insert your record here >

Set rst =New ADODB.Recordset
rst.Open "SELECT @@IDENTITY AS NewID", YourConnectionObject
lngID = rst("NewID")


see this link too

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Castaway78
ID: 38753285
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:
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.
0
 

Accepted Solution

by:
Castaway78 earned 0 total points
ID: 38755239
So... as it turns out, my underlying issue is a known bug in Access 2003/2007 when using a SQL Server back end.

Basically, what happens is that when Access queries SQL to get the Identity, it doesn't always follow the rules. For a field that once contained a value, but no longer does, Access uses Field1=NULL instead of using Field1 IS NULL.  

That's why users get the #DELETED, and yet the record did save and does exist.

Sadly, the people who created this table had no clue what they were doing, and NULL is a valid value for many fields. And since there is logic behind combo boxes that can add/remove values from fields, that triggers Access to use =NULL instead of IS NULL frequently.

Source: Bug with Access and SQL Server: Erroneous #Deleted bug on Insert with null values
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38755543
OK, then it seems that you can select your own post as the solution here
Thank you for sharing.
;-)

JeffCoachman
0
 

Author Closing Comment

by:Castaway78
ID: 38796100
"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.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now