Access VBA DAO records need to know ID field (2 Viewers)

GregoryWest · Oct 16, 2019

I have a few tables, I am running Access with multiple people accessing at the same time from different Workstations. What I need to do if execute the command: rec_dao.AddNew Then be able to find out the value of rec_dao.ID (Which is an AutoNum field)

There are no unique fields other than ID which is why I need to know what its value is as soon as the .AddNew is executed.

How do I do this?

June7 · Oct 16, 2019

The ID is not generated until data entry to another field. In VBA, just before rec_dao.Update:

Debug.Print rec_dao!ID

pbaldy · Oct 16, 2019

To be reliable I believe it has to be after the update line (I think it will work with Access tables before but not with SQL Server, but don't quote me on that). Allen demonstrates it here:

http://allenbrowne.com/ser-57.html

Dreamweaver · Oct 16, 2019

Dim LngNew As Long

Before The Line rec_dao.Update

Add the ID created to the variable LngNew= rec_dao("Your ID Fied")
you can then use theLngNew

mick

June7 · Oct 16, 2019

I tested both before and after Update. For after Update, I used rec_dao.MoveLast.

Will have to defer to Paul on SQLServer situation.

pbaldy · Oct 16, 2019

In a multiuser environment I suspect LastModified would be more reliable than MoveLast, but that's just my opinion.

Try before update with a SQL Server linked table. I don't believe it works in that setting. I'll test when I get back to my computer.

pbaldy · Oct 16, 2019

Confirming it doesn't work with SQL Server before the update (so might as well use after for portability). This:

Code:

    rs.AddNew
    rs!CarNum = 777
    Debug.Print "before: " & rs!DiagID
    rs.Update
    rs.Bookmark = rs.LastModified
    Debug.Print "after: " & rs!DiagID

produces:

before:
after: 286

theDBguy · Oct 16, 2019

Hi. Pardon me for jumping in. In either case, I prefer to use the @@IDENTITY function.

Pat Hartman · Oct 16, 2019

Here's the correct way to find the ID of the inserted row. This app started out as Jet/ACE and was converted to SQL Server and this was one of the necessary changes.

The original code picked up the ID BETWEEN the .AddNew and the .Update. That line of code is commented out in the snippet below. However, the ID isn't assigned immediately when the BE is server based so you need to run a separte query to get the generated Identity column after control returns to Access.

Code:

        'add new record
        rs.AddNew
            rs!ProcedureID = Me.lstProviders.Column(5, i) 'Me.cboProcedureID
            rs!ProviderID = Me.lstProviders.Column(6, i) 'Me.ProviderID
            rs!EffDT = Me.txtNewEffDT
            rs!PricePerUnit = Me.txtNewPricePerUnit
            rs!AppliedDT = Date                 'update providerprocedures to record that rate change was applied
            rs!ChangeBy = Forms!frmLogin!txtEmpID
            rs!ChangeDT = Now()
'            NewProvProcID = rs!ProvProcID     '''''''  Used when BE = Jet or ACE
        rs.Update
 
            Set rsScope = db.OpenRecordset("SELECT @@IDENTITY as NewID")
            '''Set rsScope = db.OpenRecordset("Select Scope_Identity as NewID") -- doesn't work
            NewProvProcID = rsScope!NewID

If you step through the code, you can see when Access generates the autonumber and it is as soon as the edit starts, just as it is on a form. If your form shows the autonumber, it starts out as (New) and when you type something into any field, the autonumber changes to the value that will be inserted.

pbaldy · Oct 16, 2019

I don't know that you "need to run a separate query". Allen's method using LastModified has worked flawlessly for me with SQL Server linked tables for years. Why is this method better and "the" correct way? Two of you are proposing it so I'm willing to learn (genuinely, if that sounded sarcastic).

theDBguy · Oct 17, 2019

pbaldy said:
I don't know that you "need to run a separate query". Allen's method using LastModified has worked flawlessly for me with SQL Server linked tables for years. Why is this method better and "the" correct way? Two of you are proposing it so I'm willing to learn (genuinely, if that sounded sarcastic).

Hi Paul. Here's all I know about @@IDENTITY If you have another method that works, there's no need to change it. Besides, @@IDENTITY will only work with Autonumber fields; whereas; .LastModified will give you access to any information you want within the new record. Cheers!

Pat Hartman · Oct 17, 2019

Perhaps "correct" was too strong a term. In many situations, other methods will also work. It really depends on how busy your users are.

Allen's method using LastModified has worked flawlessly for me with SQL Server linked tables for years

You've been lucky. It really depends on how busy the environment is. If you have two people inserting/updating data at the same time, it is possible that some other could have modified the recordset before you retrieved the "last modified".

The @@Identity command works within the active thread so it can distinguish between what this app just inserted and what some other user inserted. Technically the commented out line is the recommended solution because it ignores any inserts generated by triggers but, I couldn't get it to work from within Access. Since none of my apps use triggers to generate additional records (an automatic log would be an example of this), @@Identity is safe for my purposes.

pbaldy · Oct 17, 2019

Pat Hartman said:
You've been lucky. It really depends on how busy the environment is. If you have two people inserting/updating data at the same time, it is possible that some other could have modified the recordset before you retrieved the "last modified".

This app has a number of users adding records at the same time (5-10). LastModified is a property of the recordset, and per MS:

A record must be added or modified in the Recordset object itself in order for the LastModified property to have a value.

so I don't think it's possible to get somebody else's key value. Perhaps if you requeried the recordset I suppose, but the text above would seem to exclude that as well. Or maybe I'm just lucky.

June7 · Oct 17, 2019

Just did a test and verified that MoveLast approach would not work. Records added by other users since recordset object opened are pulled in when MoveLast executes.

Pat Hartman · Oct 18, 2019

LastModified is updated as well as inserted. Access refreshes the recordset automatically and although it won't automatically bring in new records when that happens, the whole point is to get updates made by others. Timing is everything. 5-10 people would be hard pressed to cause conflicts like this unless they were trying and that's why this technique always seems to work. And maybe it really does always work with Jet/ACE. Access is single threaded (I think) so I don't know if a recordset refresh could happen as the code is running. Although we do use DoEvents in long running procedures to allow Access time slices to do housekeeping like updating the display. Someone who knows exactly how the database engine works would have to chime in. I do know that in my mainframe work, I did have to worry about stuff like this and so I assume I have to worry about it with Access. It is safer to assume I have to worry and to use the technique that ensures the answer relates to MY current thread than anything anyone else is doing.

pbaldy · Oct 18, 2019

I guess we're interpreting "in the Recordset object itself" differently. I take it to mean that LastModified will only pick up activity that happened in the same recordset (and thus the same user). It can't/won't pick up something another user did.

The_Doc_Man · Oct 19, 2019

Access is single threaded (I think) so I don't know if a recordset refresh could happen as the code is running.

Pat: Confirming the very narrow statement that Access is single-threaded, with a caveat. Access is single-threaded but JET and ACE are not necessarily so. Both of the engines can simultaneously do a query AND the cleanup of previous actions if your system supports multiple threads. The Access user interface is single-threaded. When dealing with JET or ACE, the interface must wait for the results, so the homogeneous Access split DB appears to be totally synchronous. But that appears to be an artifact of the fact that you don't get be an acknowledgement of the results until the engine says "OK."

We had a long-winded discussion about this within the last couple of years on this forum.

Access is capable of triggering SQL queries in PASS-THRU mode and moving on to something else. They are synchronous only until the query is acknowledged from the BE. After that, it is possible to ask for the query's status through the recordset object. And SQL Server absolutely IS multi-threaded if the server is multi-thread-capable.

The above is a distillation of several articles, not always limited to this forum.

Access VBA DAO records need to know ID field (2 Viewers)

GregoryWest

Registered User.

June7

AWF VIP

pbaldy

Wino Moderator

Dreamweaver

Well-known member

June7

AWF VIP

pbaldy

Wino Moderator

pbaldy

Wino Moderator

theDBguy

I’m here to help

Pat Hartman

Super Moderator

pbaldy

Wino Moderator

theDBguy

I’m here to help

Pat Hartman

Super Moderator

pbaldy

Wino Moderator

June7

AWF VIP

Pat Hartman

Super Moderator

pbaldy

Wino Moderator

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread