Solved

Get SQL Identity via Access 2007

Posted on 2013-01-07
7
504 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

623 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