Solved

Get SQL Identity via Access 2007

Posted on 2013-01-07
7
495 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query logic question 14 71
Fields don't match on a query 9 46
Intermittent OleDbConnection Error 20 53
Conditionally trimming using VBA 8 36
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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