multiuser environment: get autonumber value of last inserted record

hey guys,

thanks for all your help all this while just wanna say first = )

got a quick question. i know how to get the autonumber value of the last inserted record - just use dmax.

however if this is a multiuser environment, we may face concurrency issues if other users are also inserting records. how do we get the autonumber of the record we just inserted?

one solution i've thought of is a elookup (or dlookup) and using the WHERE to match fields which make the record unique.

however, if the record has no way of being unique other than autonumber, what is the best way of getting this inserted record's autonumber given that it's a table with high transactions thus concurrency issues?

i instantly think of table level locking but is this the right way? and is there any other way other than table locking?

thanks guys! = )
developingprogrammerAsked:
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can get the last number this way:

rs.Update
rs.Bookmark = rs.lastmodified
<variable> = rs![<some field>]

Jim.
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<i instantly think of table level locking but is this the right way? and is there any other way other than table locking?>>

 BTW, you never want to lock an entire table unless you absolutly have to and the case where you do are extremely rare.

Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I have yet to see (that I recall) a solution that ... is guaranteed 100% to give you the correct last inserted AN  in a multi-user environment.

Here is one approach in VBA code:

Dim lLastAN As Long
With CurrentDb.OpenRecordset("SomeTableOrQueryName", dbOpenDynaset)
    .AddNew
    ![SomeFieldName] = "SomeData"
    'more code
    .Update
    .Move 0, .LastModified 'Goes to most recent record added
    lLastAN = ![YourAutoNumberField]
    .Close
End With
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I use MX's solution when using a recordset to create the record.

If not using a recordset,  I add additional fields for a time stamp, computer name,  and user  that I use to identify the last record added.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
if the record has no way of being unique other than autonumber,
If that's the case, then I'd say you've got something wrong in the data store design- otherwise, you're storing records which are identical (since the AN field should not have any "data value").
0
BitsqueezerCommented:
Hi,

the simplest way (if you using a recordset) is to look into the ID field BEFORE you use ".Update". Whenever you change any field the autonumber field will immediately be filled so that you can read that out securely before you use "Update".

Another way is to use the @@IDENTITY variable which is available since Jet 4.0 (and also in SQL Server). Here's a way to use that in ADO:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
   
    If Nz(Me.Rng_Nr, 0) = 0 Then
        Set cn = New ADODB.Connection
        Set cn = CurrentProject.Connection
        If Not cn Is Nothing Then
            Set cmd = New ADODB.Command
            cmd.ActiveConnection = cn
            cmd.CommandText = "INSERT INTO tblRngNr (Dummy) VALUES ('X')"
            cmd.CommandType = adCmdText
            cmd.Parameters.Refresh
            cmd.Execute
            Set rs = New ADODB.Recordset
            rs.ActiveConnection = cn
            rs.Open "SELECT @@IDENTITY"
            Me.Rng_Nr = rs.Fields(0)
        End If
        Set cmd = Nothing
        If Not rs Is Nothing Then
            If rs.State = adStateOpen Then rs.Close
            Set rs = Nothing
        End If
        If Not cn Is Nothing Then
            Set cn = Nothing
        End If
    End If
End Sub

Open in new window


Cheers,

Christian
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<
the simplest way (if you using a recordset) is to look into the ID field BEFORE you use ".Update".
>>

 You can't do that with SQL.  You can do it with JET, but not with SQL which doesn't assign a value until after the record is inserted as you showed with the ADO code.

The method I and MX posted works in all cases.

The only restriction is that the recordset needs to support bookmarks  (which most do).

Jim.
0
BitsqueezerCommented:
Hi Jim,

maybe you did not read exactly or I've written in a misunderstandable way but I didn't said anything about SQL in the first paragraph which you quoted. I wrote about two different solutions and the first one is really that simple (to slightly change the code of mx above):

    .AddNew
    ![SomeFieldName] = "SomeData"
    'more code
    lLastAN = ![YourAutoNumberField]
    .Update

Open in new window


The second way I wrote above is more interesting if the INSERT would be done with SQL and not with a recordset and works in the same way with Access and SQL Server.

Cheers,

Christian
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< I wrote about two different solutions and the first one is really that simple (to slightly change the code of mx above):>>

 I was simply pointing out that if you upsize to SQL server in the future or are currently using SQL server, that modification won't work.

 You can only grab the autonumber value before the update if your working with JET.

  Since many Access applications today don't use JET at all, I thought it should be pointed out.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I should add that one of the things I've learned over the years is that you need to be careful in how you answer something because "Access" can mean so many different things to different people.

Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"You can only grab the autonumber value before the update if your working with JET. "
If I understand what y'all are saying ... when the AN is actually 'available' ... that behavior has changed over the versions/years of Access.  And I've seen some VERY odd, explainable behavior trying to grab the AN before the record has been saved.
Just sayin' ...

And I'm not certain that the method we showed is 100% bullet proof in all cases - especially with a large number of simultaneous users.

mx
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<And I'm not certain that the method we showed is 100% bullet proof in all cases - especially with a large number of simultaneous users.>>

 I've never seen it fail yet.    There were some problems with AN's back with A97 with the same key being handed out for multiple users, but I haven't seen or heard of that happening since JET 4.0.

 But that was the AN process itself and not the code shown failing.

Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well right ... I wasn't imply the actual code failed.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I was just trying to be be clear for anyone reading this in the future and not trying to be redundant, but saying it another way:

I've never seen the .LastModified property point you to the wrong record, but I have seen the last record be assigned the wrong key by the AN process prior to JET 4.0.

Jim.
0
developingprogrammerAuthor Commented:
hey guys, thanks for all your help on this. i'm going to go through this one more time and close it.

you know guys, everyone here has helped me so much in the last few months and i'm very appreciative. i've realised that the way i've been approaching Access has been completely, completely, completely wrong. i've been taking it as VB 6.0 or something (i've not used VB before but this is what i think according to what i've read) where it's completely free form.

Access has frameworks built into it already which makes it a RAPID application builder. for example why use form headers and a label when i can already use form.caption? well of course it will look nicer but when i'm trying to get something up and running first, i think the most important thing is to get a working model first.

i've been trying to dismantle Access all this while due to 1) my utter lack of experience in programming, 2) my unwavering desire to create something spectacular notwithstanding any limitations.

is it possible to create something spectacular in Access EXACTLY the way i want it? well yes it is. but i'll take 1 year instead of 2 weeks. is it worth it? is it worth losing my job over this? of course not. but when we don't know the boundaries personally we need to explore and the only thing that is guiding us often is the sense of idealism.

well to cut the long story short, i've suffered extreme pain this period but i've picked up some really, really important skills and i've got to learn some really, really critical skills in terms of USING Access. not using VBA. Access is Access. VBA happens to reside in Access. i've got to work with Access.

you know, when i allow myself to actually work with the framework suddenly everything becomes so much easier. as per what you ALL have told me many times before (but i had to make this mistake cause i had to gain the experience --> at least though yall telling me this helps me IMMEDIATELY connect the dots when i've gained enough painful experience) and what TheHiTechCoach has shared so succinctly,

Creating solutions with Access is much different than using VB/VB.net. Access provides a framework with most of the plumbing already done for working with a database. You add controls to forms/reports with no coding required.  On a bound form, most of the work for adding/editing/deleting records is handled by Access without you having to write a single line of code. The trick is knowing where sprinkle a few lines of VBA code in different events to make it very powerful.

well i guess on the bright side i've gained thought cycles in going through in my head how to build an entire plumbing system myself and though it has been most, most, most, most, most excruciatingly painful (really unbelievably so guys in my situation), i've learnt so much and guys, i see hope now. work involved to finish thing "thing" as Christian has most rightly termed it ha, but at least after this gruesomely hideous monster has been completed, there is hope in creating a simple, simple, simple, simple, simple application.

suddenly the air seems so clear. just thinking about naming my PKs of the tables, having simple queries and forms, a very simple navigation system for my UI where a button just opens a form (doesn't close the previous one), it is so refreshing and hopeful. i felt that when i was trying to do something that required 1 point of energy, i expanded 100 points of energy. the good thing though is, now i know. i know guys. i know where my mistakes are and what was the problem. and now, i suddenly have 100 x more energy cause i'm not wasting it trying to fight things anymore.

thank you so much for all your help guys, i had to join excruciatingly painful experience with your readily and freely given advice to finally understand things. i'm so tired guys but now i've got 100 x more energy ha. thanks so much for guiding me all this while. this big lesson i've learn will be applied to everything else i do - objective C is a programming language for certain controls, SQL is a language for certain databases. i gotta stop trying to create the whole building and plumbing system every single time. sprinkling of code here and there is fine but other than that, that's about it. thanks so much once again guys = )
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I think you've gained a good and through understanding of Access and how it's meant to be used.

It does so many things for you, which can be either a blessing or a curse. Yes, you can add a little bit without too much problem.   You can even add a little bit more and it will only fight you a bit.   Add more though and it will start to fight you a lot.  And if you need to add more beyond that, then you need to ask why your bothering to use Access in the first place.

A lot of VFP folks make the same mistake when they first start using Access.  Their so used to doing everything  and they automatically try to work Access the same way.  

If you've used/developed with anything else, you just need to drop what you know and work with it as is (for the most part) and if you can't get it to do what you want it to do, then you need to use something else.

 Glad to hear that you closing in on the end of your project.  It's been fun to trying to answer many of your questions!  It's quite obvious that your very passionate about your development.

Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"you know guys, everyone here has helped me so much in the last few months and i'm very appreciative"

Don't forget one important thing. You've helped yourself a great deal - in *wanting* to learn with a great attitude.  This fact has made it a pleasure for us to help you along the way - and into the future. And of course, we not doubt have learned a few more things also ... almost a daily occurrence on Experts Exchange :-)

mx
0
developingprogrammerAuthor Commented:
thanks so much guys. you know what's the most amazing thing about Experts Exchange? it's the community and the encouragement / want to help beginners like me. in the office everyone just cares about themselves and no one wants to help. but here, when we ask a question, there are always so many experts willing and waiting to help. and more importantly the care and concern us beginners feel by having people that are even willing to help us = )

thanks so much guys!! i'm not done with my project yet and i'm definitely not done with my learning yet. have got quite a few more questions to ask but i'll get there soon = ) let me get back to yall on this question now = )
0
developingprogrammerAuthor Commented:
hi Christian! thanks for the wonderful ADO code you posted above. i think that looking into the AN field before .update is probably the easiest way of doing things (when we're working with JET and DAO as Jim shared - and you know i only work with JET now and that's probably why you catered the solution to me the asker = )   )

the ADO code looks very cool and i guess cause at the moment i'm not too familiar with ADO i need to put a little more time to understand it moving forward. but i want to use disconnected ADO recordsets so i definitely want to pick this up Christian = )

Jim and mx, thanks so much for your fantastic response on using bookmarks! till date i've not used bookmarks actually (i think largely because i've been querying the database and only retrieving records that match what i want instead of pulling a big recordset and then moving to a certain record - i do understand that sometimes we can pull a smaller set of data and move within it and that would be perfect for bookmarks just that i haven't come across that scenario in my usage till date - also bearing in mind that i've been fighting Access quite a bit ha). but i think bookmarks are a very critical tool and i will have to start using them.

what's going on in my head now is - keep things simple, stupid! so i'll just use all the ready made Access functionality and if the query is running too slowly on the network then ok, i'll pull a smaller recordset with parameters then or something like that. many times i over think things and whilst it's good for scalability and looking into the future, i also need to develop the skill of adapting to the present now - and that's where keeping things simple is key, and when things need an upgrade then i'll upgrade them = ) i think a good mix of scrumming with doing things very scalable in the first iteration is the ideal balance. but that comes with experience which we slowly chaulk up as we develop more = ))
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<and if the query is running too slowly on the network then ok, >>

 With JET as a backend, indexing is crtical.   If you have a long running operation and it's used often, make sure your indexes support it.

 If your trying to find records, consider using .Seek on an index, which hands down is the fastest way to locate a record.

There is also a setting for JET called MAXBUFFERS, which you might want to increase.  This controls the size of the JET cache.

Jim.
0
BitsqueezerCommented:
Hi,

what happened to your goal to create the "ideal framework" and to always use the best way of doing things? Not that I would not agree with your conclusion of keeping things simple but for me it sounds a little bit that something has happened which turns your thoughts into a new direction, maybe because someone forced you to give up your plans.

Anyway, I hope you can reach your goal of finishing your project successfully and I also hope that you go on with researching all the backgrounds like in the past months maybe in your private environment, I think you was on a good way to become the next expert here to help others with all that you've learned, so don't give up your enthusiasm even if someone has broken your initial plans in your job. Maybe there comes the time when you are the one who establish a company which creates great software and you are the boss to tell others what they should do in the way you want.
Never give up a dream.

Cheers,

Christian
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"ideal framework"

I don't think that exists per se.

mx
0
developingprogrammerAuthor Commented:
thanks Christian, you understand me very well.

well things are looking quite non-sanguine to be euphemistic but just like what you've said Christian, never give up on a dream. hrmm, well reality is checking in on me and i may be going into a period soon where i may need to survive first before working towards the dream. but i guess the silver lining is - this is not a dream anymore. as long as i can survive, this is my reality - that i want to create something, build something and manufacture value. my ideas may be small now (like an assets tracking accounting system of all my life belonging in my small room and a personal finance system) and perhaps not so commercial, but at least these would build up the skills necessary for things in the future.

Christian you've given a lot to me, and so have all the experts here in EE. i definitely want to give back as well to the EE community = ) i think moving forward though, because development plans at work will come to an end by year end, the only environment i can develop in will be iOS cause it will be a private environment - unless i can find clients outside for Access. ideally, i would love to be skilled in the suite of Access, iPhone, iPad and websites. this covers Microsoft, Apple and databases (websites).

so long as i can put food on the table (and now too pay for my friend's spine treatment) i will continue developing. it's... an environment where i can go into "dreamland" where everything can be good and where there can be love cause we can do things for others = ) thanks for your encouragement Christian = ) i promise i won't give up!
0
BitsqueezerCommented:
Hi,

unfortunately I cannot help you with anything about Apple, but if you are fit there you can earn a lot of money in creating good apps (also with other OSes like Android or WinPhone).

i promise i won't give up!

That's what I wanted to hear from you...:-)
Hope you'll find your way.

Cheers,

Christian
0
developingprogrammerAuthor Commented:
= )) but my roots always started in Access and VBA, from what you taught me!! = ))
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
Microsoft Access

From novice to tech pro — start learning today.