How return details of updated row?

Question

I have database in MS Access 2016, and want to run update query thru Excel VBA - this works. After this I want to return details of updated row (values from two columns - ID, Col1). Now I have only number of affected rows. How to achive this?

My query:

UPDATE 
(SELECT TOP 1 ID, Col1, Update_time, Update_user 
FROM Table1 
WHERE Update_user Is Null 
ORDER BY Col2 DESC , ID)  AS U_ROW 
SET U_ROW.Update_time = Now(), U_ROW.Update_user = [username];

In Excel VBA I run it thru ADODB.Command:

    With baseRecordsetCommand
        .ActiveConnection = objectConnection
        .CommandType = adCmdStoredProc
        .CommandText = "qryTest"
        .NamedParameters = True
        .Parameters.Append .CreateParameter("@username", adVarChar, adParamInput, 255, LCase(Environ("Username")))
        .Execute recordsAffected
    End With

Answer 1

Usually these kind of ops goes into a stored procedure but in Ms Acces/Excel you have to use VBA. Easiest way is, to retrieve the Id you are about to update before you the update.

So this comes first: save it in a variable

SELECT TOP 1 ID
FROM Table1 
WHERE Update_user Is Null 
ORDER BY Col2 DESC , ID

And then

update Table1
SET Table1.Update_time = Now(), Table1.Update_user = @username where Id = @idFromFirstQuery;

followed by

select * from Table1 where Id = @idFromFirstQuery;

in this way you know what Id, row you are updating.

OR:

you can turn the now() also into a parameter and supply a timestamp as parameter. like:

update (table selection ) set update_time = '2020-19-03 10:0=10:10', update_user = 'User1';

after update you can do something like: Select * from Table1 where update_user = 'User1' and update_time = '2020-19-03 10:0=10:10';

NOTE!! you are highly assuming the timestamp you are supplying + username is unique. I personally would not use this method.