UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using DLookup to find the @@IDENTITY of a new Insert    
 
   
Stherrien
post May 30 2008, 12:49 AM
Post#1



Posts: 34
Joined: 3-April 08



I'm copying a purchase order, which is a PurchaseOrder(PO) record and 1+ item (POItems).
I have some sql that you pass an Identity in and a near identical record is inserted into the table (pretty much the same record with a different Identity).
Now After this, I need to do a similar query where I find all POItems that belonged to the PO by ID and make copies of the records but with the new PO's ID.
So in between the two SQL commands run, I need to retrieve that new records Identity.
Online in my searches I've found that you run "SELECT @@IDENTITY" to get this, which is what you do in SQLServer. Now the only way I know how to Select into a value is with DLookup:
' Inserts a new PO into table POTable
DoCmd.RunSQL (strCopyPO)
' Retrieves newly inserted identity.
strNewPOID = DLookup("@@IDENTITY", "POTable")
My first realization is that DLookup requires a table... but "SELECT @@IDENTITY" doesn't have a table in the statement, so I used the table I was selecting from. Maybe theres a system table?
Oalways get the value of 577 returned, no matter how many records I copy. There are over 6,000 records in POTable and the ID is up over 6,000 right now.
So, can anyone please give me some advice on how to retrieve this last value and place it into a variable? Thanks very much in advance sad.gif
Go to the top of the page
 
GroverParkGeorge
post May 30 2008, 01:35 AM
Post#2


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


See if the information in this KB Article helps.


I've also had success with the following syntax to retrieve the identity value:

Dim rst as DAO.Recordset

(Your other code here to insert records)

Set rst = CurrentDb.OpenRecordset("Select @@Identity as IdentityColumn from POTable")
strNewPOID = rst!IdentityColumn
set rst =Nothing


You need to alias the identity value in the SQL to retrieve it from the table so that you can address it properly in the returned recordset.

George
Go to the top of the page
 
LPurvis
post May 30 2008, 03:27 AM
Post#3


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


I agree with George - you shouldn't be attempting to use a doman function for this. A recordset is the way to go.
The @@Identity function is connection dependent though - so it doesn't matter where you specify it to be from, you'll get the last inserted identity (autonumber) value through that connection / session (i.e. workspace).

And you don't always need to alias - it just depends upon how you reference your recordset's field.
(Ordinal position is, in theory, slightly faster than item name).

Giving us:

Set rst = CurrentDb.OpenRecordset("Select @@Identity")
strNewPOID = rst(0)
rst.Close
Set rst = Nothing

Has another syntax option (though essentially identical).

Cheers.
Go to the top of the page
 
LPurvis
post May 30 2008, 03:42 AM
Post#4


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Looking back at the question (sometimes I almost skip them lol :-s) It's worth bearing in mind - that Access itself maintains a connection to your data (dropped and created as required).
The Workspace provided in DAO (and a connection object in ADO) do not share that exact same connection.
They are exposed to a copy of it.
So what?
Well - @@Identity is (as I mentioned previously) connection dependent. Consequently any inserts you create through the Access UI or using Access methods won't be exposed by a call to Identity through a DAO (or ADO) recordset as has been shown in our examples.
If you're making form updates - you'd need a form explicitly bound to a recordset to be able to then retrieve the @@Identity value.
If you're executing action queries (i.e. an Insert) then you'll need to make sure you execute that Insert statement through a data API (DAO or ADO) that you're then retireving the value with.
In other words - don't use RunSQL or OpenQuery. :-)
If you execute your query with
CurrentDb.Execute
then you'll be able to do as has been shown and get the value you require.
(It's generally the preferred way to execute action statements anyway).
I just thought I'd mention all this - pre-empting your next question once you'd implemented the suggested changes and received 0 as the returned value. wink.gif
Your code might then look like
CODE
With CurrentDb
    .Execute strCopyPO
    Set rst = .OpenRecordset("Select @@Identity")
End With
strNewPOID = rst(0)
rst.Close
Set rst = Nothing

thumbup.gif
Go to the top of the page
 
Stherrien
post May 30 2008, 07:37 AM
Post#5



Posts: 34
Joined: 3-April 08



Well first off, thank you all.
I'm still not familiar with all these Access conventions (SQL Server developer by trade) so things like this just stop me up.
Yes, I did get that 0 error, then using the CurrentDB.Execute method for my initial SQL rather than DoCmd.RunSQL as suggested fixed that and I now have the ID after an insert!
Ocan't believe how hard that was to figure out... like I just didn't know exactly what to google to get the answer or I just couldn't find it.
Thank you very much.
Go to the top of the page
 
LPurvis
post May 30 2008, 08:04 AM
Post#6


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


No worries.
It's true it's far simpler in SQL Server (where we'd likely use Scope_Identity() anyway of course ;-)
@Identity was introduced in Jet in version 4.
However you have to bear in mind that Jet is file server based - and consequently is all about each other the indivudal connections made to it.
Access hides a lot of the simple integration with Jet - but when you start pushing below the surface you need to become aware of the subtle distinctions.
Glad you're sorted anyway.
Go to the top of the page
 
GroverParkGeorge
post May 30 2008, 10:16 AM
Post#7


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


Ah, yes. I knew I was forgetting something last night: rst(0). (Never good to post two hours after normal bedtime.)
Go to the top of the page
 
LPurvis
post May 30 2008, 10:19 AM
Post#8


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Ahh - a bedtime... That sounds like a nice thing to have. sleeping.gif
Go to the top of the page
 
Stherrien
post May 30 2008, 10:43 AM
Post#9



Posts: 34
Joined: 3-April 08



I knew Access was file server based and about the connections. I hadn't realized that this was the case when you were inside of MS Access on an Access Form though.
thought part of the issue was getting everything in the same scope, but from VBA the only way I've really run queries/sql is RunQuery, RunSQL & DLookup.
Now, seeing this way to run queries through database.execute and openrecordset, I see a way to bring multiple queries together on the same connection... and OpenRecordset seems a bit more natural to me than DLookup with this quasi query you're building through parameters.
Thanks a bunch to both of you. I got a problem solved and learned something really useful sad.gif
Go to the top of the page
 
GroverParkGeorge
post Jun 1 2008, 03:20 PM
Post#10


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


Me, too. Learning stuff, I mean.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 02:54 PM