Need to retrieve the most recently inserted row after running an append query.

In a code module within Access, I use a SQL statement to insert a new row in a table.

The table has an autonumber field as the primary key.

I need to retrieve the primary key from the newly appended row.

This is what I have:

Sub InsertRow()

DoCmd.OpenQuery "qapdTickets", , acReadOnly

'Find the autonumber field value for the newly appended row.

End Sub

Any hints would be appreciated.

Thanks
DovbermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
if this is a single user application, you can use


lastinserted=dmax("AutonumberField","tablename")
0
dqmqCommented:
But, I will describe the more general solution. Understand that the autonumber field is a surrogate key, which means it has NO business meaning. If the field does have business meaning, then please do not use autonumber.

With that out of the way, there needs to be some other combination of columns with business meaning that uniquely identifies a row. In fact, the best practice is to have a unique index on the combination of columns.  Also, it is my practice to use that index, instead of the autonumber for the primary key. But I digress.  Anyway, in your code, lookup the autonumber field based on the unique key.  

You can use the DLOOKUP function or a recordset to look it up.  Alternatively, sometimes it's desireable to position the form on the newly added record. IN that case, if the form is bound to the autonumber field you can just retrieve it from the form.

 


0
DovbermanAuthor Commented:
The autonumber is used only to uniquely identify the row.
DLOOKUP is too slow for a large table.
I prefer creating a recordset object that contains only the Max(ID) row.

I am having trouble creating the recordset object using Access 2003 VBA code.

Dim rstTickets As ADODB.Recordset
   
    rstTickets.CursorLocation = adUseClient  ' Error object variable not....
   
    rstTickets.Open strSQL, , adOpenKeyset, adLockOptimistic
   
    lngTicketNumber = rstTickets.Fields(0).Value

What is keeping me from creating the recordset ?

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Rey Obrero (Capricorn1)Commented:
did you try the dmax ?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
If this is an Access MDB, why are you using ADO instead of DAO ?

mx
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can also do this:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT @@IDENTITY As NewID", CurrentProject.connection

msgbox "New ID is " & rst("NewID")

rst.close
set rst = Nothing
0
DovbermanAuthor Commented:
Capricorn1 - dmax will be ok. This is a single user application.

DatabaseMX - Yes, I do need to open a DAO object, not an ADODB object.

I have a new project that uses Access instead of VB and SQL Server. It's been a long time since I used Access.

Thanks for all your help
0
Leigh PurvisDatabase DeveloperCommented:
Just to waffle on a bit... (for a change :-s)

Developing to your current needs is finea dn can be a time saver.
You'd be amazed how often applications grow beyond their original conception.

The method Scott (LSMConsulting) gave you is the standard/appropriate method (for Jet 4 onwards) for retrieving the last appended autonumber.  It takes very little implementation effort (it too can potentially be one line like a DMax is) and protects against future expansion of your applications horizons. :-)

It's important to note that the @@Identity is connection dependent.
So if you have two connection objects open to a database and insert via one - but check the @@Identity with the other then you'll get eroneous results.
The exact same is true of the two *types* of Access connection technologies.  (DAO and ADO).
If you insert via an ADO method (i.e. most likely CurrentProject.Connection) but check with a DAO recordset (i.e. using CurrentDb) then you'll get that eroneous result again.

The remaining issue to mention about it that you're currently using DoCmd.OpenQuery to run your insert.
This is an Access method which allows you to reference Access objects within the query - which is, I imagine, how you're inserting a different set of values each time.
Using this method won't expose the @@Identity function to either a DAO or ADO connection.
So - this sounds like a huge PITA huh?
It needn't be.  Once the code's written it's easily adapted to be used again and again. :-)

Assuming that you do have those form parameters in your query then consider the following as generic code.

Function fExecuteInsert(strQueryName As String) As Long
   
    Dim db As Database
    Dim prm As DAO.Parameter
    Dim qdf As QueryDef
    Dim rst As DAO.Recordset
   
    Set db = CurrentDb
    Set qdf = db.QueryDefs(strQueryName)
   
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next
    qdf.Execute
    Set rst = db.OpenRecordset("SELECT @@Identity")
    fExecuteInsert = rst(0)
    rst.Close
   
    Set prm = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing

End Function

You'd just call that as
lngNewID = fExecuteInsert("qapdTickets")
and the new ID will be retrieved into lngNewID.

FWIW the same can be achieved using ADO - and, just to mention, IMO ADO has a good part to play in development that uses a Jet (MDB) backend too.
MX's preference seems to be DAO (which is fine of course - it's my *preference* for fileserver work too) but there are times when ADO offers functionality which DAO simply can't offer - even when not taking advantage of ADO's wide datasource options.

The recordset method you mention part with through this thread is also perfectly valid.
You can open a recordset as you do.
You can retrieve the new Autonumber ID from the recordset once you've "dirtied" the record you're appending in a DAO recordset.  As soon as you call the AddNew method in an ADO recordset.  (If you're using a client side ADO recordset then you need to wait until you've updated the append!)

Done! :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DovbermanAuthor Commented:
LPurvis,

I agree with you completely. From 1991 to 1992, I worked for Microsoft on an Access product support team. For the next 3 years, I developed customized apps in Access. Yesterday, I inherited a poorly written app that needed a quick fix.

I will use the @@Identity property when the dust settles. However, I added an error handler yesterday.

Thanks,
Dovberman
0
Leigh PurvisDatabase DeveloperCommented:
No worries.
As I say - tha function fExecuteInsert will be just plug and play when you're ready.
No effort at all - but good reliability for multi-user executions as well.

(In 1991/1992 I hated computers... honestly... did for several years afterwards too! Funny how things turn out ;-)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"From 1991 to 1992, I worked for Microsoft on an Access product support team"

We have probably talked on the phone!  That was when Tech Support was affordable.  I still have the A1.0 **diskettes** :-)

mx
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My earliest version is 1.1 on diskettes ...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I have those also.  But ... what the HELL do we do with them ?  New machines don't EVEN have floppy drives, lol!

mx
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I had a machine built about a year ago, and they included a floppy drive with it ... not sure why, however!!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
'see ya' Floppies ....

0
DovbermanAuthor Commented:
If you still have the 2.0  Users Guide, that's my DAO properties and methods chart on the back cover. It's worth and extra 25 cents at any flea market.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You know ... I just recently tossed that book ... and NOW you tell me it's a collector's item !!!

Well ... at least you can say that you had nothing to do with those nasty A2007 Ribbons  (I hope) ...

mx
0
Leigh PurvisDatabase DeveloperCommented:
I'm not seeing a DAO chart on the back cover.  Just keyboard shortcuts etc...
Perhaps it was a different Guide. :-(

(In fact, the User Guide I have here doesn't seem to even deal with Access Basic at all :-s)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Actually, I think there were 2-3 white books that came in the box ... and those were good books too!

mx
0
Leigh PurvisDatabase DeveloperCommented:
<shrug>
1994 - I was still merrily doing my hard sums. :-)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Better than hard time :-)

mx
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Okay ... what are "hard sums"?
0
Leigh PurvisDatabase DeveloperCommented:
At University, it was the Department of Mathematics Women's Hockey team (I believe) who would wear sweatshirts which read
"University of Newcastle - Department of Hard Sums"

I always really liked that designation.
Would almost have liked to sign up myself just to get the shirt lol - I already had the ambiguously spelled name I suppose :-p
(Though the beard I sported back then might have given me away).
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Women .... who would wear sweatshirts which read ... Hard Sums"

Sooooooooooo, ahhh ... one might now ask what is meant by the term .... 'doing' ... LOL.

mx
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, mx ... can't wait for clarification on that little slip o' the tongue (so to speak) <g> ...
0
Leigh PurvisDatabase DeveloperCommented:
Ha, for likelihood of *that* interpretation - see previous "Beard" comment.
(And substitute it with "Stupid Beard"... among other things :-s)"
0
DovbermanAuthor Commented:
My 2.0 User's guide is somewhere in the Gulf of Mexico thank's to Katrina. I live in Biloxi.
My flood insurance paid $2000 for a desktop computer of the same vintage. That helped balance out the uncovered items.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.