.execute then .openrecordset

[shura30]

So I'm having this issue where I can successfully update a row but fail to retrieve one of its fields immediately after.

I have this piece of code in a module:

Private objMyDB As DAO.Database
                      
Public Function myDb(Optional bolRefresh As Boolean = False) As DAO.Database
                      
    If objMyDB Is Nothing Or bolRefresh = True Then
        Set objMyDB = CurrentDb()
    End If
                      
    Set myDb = objMyDB

End Function

then in the form class I've defined a function to populate a form, I've also defined a transaction to be sure to not mess things up as this is a multiuser database (be/fe split, everyone has their own fe):

    DAO.DBEngine.BeginTrans
    
    someUpdateQuery = "UPDATE sometable SET locktime = " & unixTimeVariable & ", " lockedBy = """ & Username & """ WHERE somecriteria

    myDb.execute someUpdateQuery, dbFailOnError
    
    if myDb.RecordsAffected > 0 then
        lockQuery = SELECT id WHERE the above locktime AND the above username
        set rs = myDb.openRecordset(lockquery, dbOpenDynaset, dbReadOnly)
        
        if not rs.EOF then
            lockedID = rs.fields("ID").value
        end if       
    end if
    
    If IsNull(lockedID) Or lockedID = "" Then
        DAO.DBEngine.Rollback
    exit sub
    end if

    DAO.DBEngine.CommitTrans

this transaction gets rolled back at the lockQuery point, I've debug printed myDb.recordsaffected and it shows 1 (as it should be) but the lockQuery doesn't retrieve a value UNLESS the referred table is open in the background.

where am I getting things wrong here?the code is pretty simple but I feel I'm missing something

 

[theDBguy]

Is lockedID an Autonumber field?

 

[Gasman]

Perhaps put " around your select query?

 

[shura30]

Is lockedID an Autonumber field?

The 'ID' is the auto number

Perhaps put " around your select query?

I typed the code just to have an idea, we can exclude syntax errors

 

[Gasman]

Well every time I use/have seen a sql string, it is surrounded by quotes?
Not hard to test is it?

 

[The_Doc_Man]

The UPDATE is made after a "Begin Transaction" but the SELECT occurs before the "Commit Transaction" - so all of the proposed updates are still pending at that time and I think you therefore might be getting back the SELECT info from the records before that transaction takes effect. Maybe? I'm not 100% sure about this case because I don't do Access Begin/Commit transactions that often - but that is the first thing I noticed.

 

[theDBguy]

The 'ID' is the auto number

I was thinking along the same lines as Doc. Try doing a CommitTrans before opening the recordset just to see if anything changes.

 

[shura30]

The UPDATE is made after a "Begin Transaction" but the SELECT occurs before the "Commit Transaction" - so all of the proposed updates are still pending at that time and I think you therefore might be getting back the SELECT info from the records before that transaction takes effect. Maybe? I'm not 100% sure about this case because I don't do Access Begin/Commit transactions that often - but that is the first thing I noticed.

I was thinking along the same lines as Doc. Try doing a CommitTrans before opening the recordset just to see if anything changes.

Then why would this work flawlessly if the table is already open?

 

[Josef P.]

The logic with the transaction fits. It should be possible to display a data record before committing.
I suspect that the code will not run even without a transaction. The problem will lie in the code blocks that are not shown (where there is currently only pseudo code).

Note:
However, I would write Application.DBEngine (or only DBEngine) instead of DAO.DBEngine. That's more descriptive.

 

[The_Doc_Man]

Then why would this work flawlessly if the table is already open?

OK, I looked this one up and remembered what it was that I was thinking about. This is "standard" SQL behavior, subject to vendor-specific implementation limits.

When you start a BEGIN TRANSACTION block, you can see your own updates but they are not visible to anyone else until you issue the COMMIT TRANSACTION to end the transaction block. So scratch my original suggestion.

I'm going to suggest a slightly different approach. Instead of trying to open a recordset based on a query, open the recordset based on the table and then do a .FindFirst on your two criteria. Don't test for EOF, but rather test for NOTFOUND. It might make no difference at all, but maybe it would work.

Can you clarify something for us? If this works if the table was already open, WHY was it already open? Do you have another recordset active to it?

 

[ebs17]

I see two things:
- Filtering on a DateTime value can quickly go wrong because of the floating point problem for the time values.

if not rs.EOF then
     lockedID = rs.fields("ID").value
else
     ' look for big surprise
end if

- An update query does not create a new ID, but rather it is already set if there is a record for filtering on somecriteria. In this sense, the transaction itself is also completely overdone.
Using a recordset action, you can handle what has been shown in one step, for example

Set rs = myDb.OpenRecordset("SELECT * FROM sometable WHERE " & somecriteria, dbOpenDynaset)
With rs
    If .EOF Then
       ' nothing
    Else
       lockedID = .Fields("ID").value
       .Edit
       .Fields("locktime") = unixTimeVariable
       .Fields("lockedBy") = Username
       .Update
       bFlagSuccess = True
    End If
    .Close
End With

I assume that at most one record is selected via somecriteria.
When creating a new record, the ID would be available immediately after AddNew.

If IsNull(lockedID) Or lockedID = "" Then

What is the data type of lockedID)?
For an autonumber one would use Long, so checking for NULL would throw an error, and checking for a nullstring is pointless anyway.

 

[shura30]

Can you clarify something for us? If this works if the table was already open, WHY was it already open? Do you have another recordset active to it?

While debugging I tried with and without the transaction to see where the code is failing, at some point I've opened the table in the background to see in real time the records affected, that's where I noticed it would work and the lockQuery would actually return a value instead of null

- Filtering on a DateTime value can quickly go wrong because of the floating point problem for the time values.

I stopped using date/time and moved to unixTime, the latter is just a number, much simpler to work with

What is the data type of lockedID)?
For an autonumber one would use Long, so checking for NULL would throw an error, and checking for a nullstring is pointless anyway.

lockedID is a variant
I'm checking for null as it's what is actually happening, the lockQuery isn't able to pick an item so returns null for the rollback
checking for the empty string is pointless tho, I'll remove that, was using it for testing purposes


as silly as it may sound, looks like myDB.execute and myDB.OpenRecordset are working on different databases/tables
one where the record is able to get affected (hence myDB.RecordsAffected returns 1), the other on another instance where that record has not been touched

 

[Josef P.]

Note:

lockedID is a variant

Dim lockedID  As Variant

' without set a value to lockedID:
Debug.Print "Null: ", IsNull(lockedID), " .. False"
Debug.Print "Empty: ", IsEmpty(lockedID), " .. True"
Debug.Print "="""": ", lockedID = vbNullString, " .. True"
Debug.Print "=0: ", lockedID = 0, " .. True"

If the ID is a number, I would declare lockedId as long and check for 0 if necessary.
Or redesign the code a bit:

Private Sub YourProc()

    Dim lockedId As Long

    DAO.DBEngine.BeginTrans

    If Not TrySomething(P1, P2, ..., lockedId) Then
        DBEngine.Rollback
        Exit Sub
    End If

    DBEngine.CommitTrans

    ...

End Sub

Private Function TrySomething(byval Param1 as .., ... , ByRef lockedId As Long) As Boolean

    ...

    someUpdateQuery = "UPDATE sometable SET locktime = " & unixTimeVariable & ", " lockedBy = """ & Username & """ WHERE somecriteria
    myDb.Execute someUpdateQuery, dbFailOnError

    If myDb.RecordsAffected > 0 Then
        lockQuery = SELECT id WHERE the above locktime AND the above username
        Set rs = myDb.OpenRecordset(lockquery, dbOpenDynaset, dbReadOnly)
    
        If Not rs.EOF Then
            lockedId = rs.Fields("ID").Value
            TrySomething = True
        End If
    End If

    rs.Close

End Function

 

[shura30]

this is the piece of code, most of the function that does the locking/reading of the table:

    On Error GoTo ErrorHandler

    Dim locking As Long
    Dim lockQuery As String
    Dim selectAndLockQuery As String
    Dim lockedID As Variant
    Dim rs As DAO.Recordset

    ' Impedisce la chiusura del form senza aver prima salvato o rilasciato il record
    TempVars("canClose") = 0

    ' Ottieni l'ora corrente formattata per il blocco del record
    locking = DateDiff("s", #1/1/1970#, Now())

    ' Inizia una transazione per garantire atomicità

    Application.DBEngine.BeginTrans

    ' Verifica se ci sono record pendenti bloccati dall'utente corrente
    lockQuery = "SELECT ID FROM t_filerecaller " & _
                "WHERE lockedBy = '" & TempVars("Username") & "' AND stored <> 1"

    Set rs = myDb.OpenRecordset(lockQuery, dbOpenDynaset, dbReadOnly)
    If Not rs.EOF Then
        ' Se esiste un record bloccato dall'utente corrente, utilizza quel record
        lockedID = rs.Fields("ID").Value

    Else
        ' Se non esiste, seleziona e blocca un nuovo record
        selectAndLockQuery = "UPDATE t_filerecaller " & _
                             "SET lockTimeUnix = " & locking & ", " & _
                             "lockedBy = '" & TempVars("username") & "', " & _
                             "stored = 0 " & _
                             "WHERE ID IN (" & _
                             "  SELECT TOP 1 ID FROM (" & _
                             "    SELECT TOP 10 ID FROM t_filerecaller " & _
                             "    WHERE lockTimeUnix = 0 " & _
                             "    ORDER BY ID " & _
                             "  ) AS subquery " & _
                             "  ORDER BY Rnd(-(100000*ID)*Time())" & _
                             ") " & _
                             "AND lockTimeUnix = 0"

        myDb.Execute selectAndLockQuery, dbFailOnError

        If myDb.RecordsAffected > 0 Then
MsgBox myDb.RecordsAffected
            ' Se è stato bloccato un record, ottieni il suo ID
            lockQuery = "SELECT ID FROM t_filerecaller " & _
                        "WHERE lockTimeUnix = " & locking & " AND lockedBy = '" & TempVars("Username") & "'"
MsgBox lockQuery
                      
            Set rs = myDb.OpenRecordset(lockQuery, dbOpenDynaset, dbReadOnly)
            If Not rs.EOF Then
                lockedID = rs.Fields("ID").Value
                MsgBox lockedID
            End If
        End If
    End If
    rs.Close
    Set rs = Nothing

    If IsNull(lockedID) Or lockedID = "" Then
        Application.DBEngine.Rollback
        TempVars("canClose") = 1
        MsgBox "Non ci sono SR da lavorare, torno al login", vbInformation
        DoCmd.Close acForm, Me.Name, acSaveYes
        DoCmd.OpenForm "Login"
        Exit Sub
    End If

    Application.DBEngine.CommitTrans

for some reasons MsgBox myDb.RecordsAffected outputs 1
but the Or lockedID = "" gets triggered
for now I'm keeping it as variant until I solve this issue

again, if the table is opened in the background of the access UI (it's a linked table to a backend) the code executes flawlessly and the form attached gets its data



edit:
out of desperation I changed from:

Set rs = myDb.OpenRecordset(lockQuery, dbOpenDynaset, dbReadOnly)

to

Set rs = myDb.OpenRecordset(lockQuery)

and now it's working, why?

 

[The_Doc_Man]

That syntax (defaulting recordset type and flags) normally makes the recordset type use dbOpenTable by default, but since you presented a query (not a table), that would become a dynaset anyway. So that part of your change is probably - as we sometimes say - a "nothing burger."

But the OTHER thing you mentioned was that you didn't specify dbReadOnly, which means it would take out a different kind of lock on the recordset. I don't pretend to know why it wants to write to your recordset, but if that makes a difference then apparently it DOES want to write something. Damned if I know what or why, but ... by observation, if that change made it work, then something about your recordset needed to be read/write capable.

Unless I missed it in post #14, you ALSO open "rs" twice in the same routine without closing it in between the two opening attempts. The documentation on OpenRecordset suggests you should close it if you are going to open it again. Otherwise, you might get confusing results.

 

[shura30]

Unless I missed it in post #14, you ALSO open "rs" twice in the same routine without closing it in between the two opening attempts. The documentation on OpenRecordset suggests you should close it if you are going to open it again. Otherwise, you might get confusing results.

I'm improving the code as I go, I've noticed that as well and made some other big changes

I had another version of this code, was using date/time for the locktime field and everything was working as intended
as soon as I switched to unixtime to not have to deal with format behavior, it stopped working until I removed the dbReadOnly