Why does DLookup not return the values updated during a transaction?

Question

I am using transactions in my MS Access VBA code. I update one of my fields, the Incoming_Pieces field in my Inventory table. Then, before committing the transaction, I use DLookup on the updated field. The DLookup function will return the value before the transaction, not the value updated during the transaction before committing.

Is this intended? There's no error message or any warning that the data I am retrieving is out of sync with the data being updated within the transaction.

Is the only workaround to generate a SELECT statement instead of DLookup?

Here's my testing code to prove this.

Public Function testTransaction()

    Dim pieces As Variant
    pieces = DLookup("Incoming_Pieces", "Inventory", "Code='MT-1-1000x1x1'")
    Debug.Print (pieces)    ' <------------ prints 0
    
    DAO.DBEngine.BeginTrans
    
    Dim sql As String
    sql = "UPDATE Inventory SET Incoming_Pieces = 10 WHERE Code='MT-1-1000x1x1'"
    CurrentDb.Execute (sql)
    pieces = DLookup("Incoming_Pieces", "Inventory", "Code='MT-1-1000x1x1'")
    Debug.Print (pieces)    ' <------------ prints 0

    DAO.DBEngine.CommitTrans

    pieces = DLookup("Incoming_Pieces", "Inventory", "Code='MT-1-1000x1x1'")
    Debug.Print (pieces)    ' <------------ prints 10
    
End Function

Answer 1

DLookUp is executed in the application scope, while BeginTrans only affects the database engine scope, and only DAO.

In queries, we can make distinctions between 2 scopes:

1. Application. These queries can make use of user-defined functions and can refer to form-based parameters (such as WHERE MyField = Forms!SomeForm!SomeControl).
2. Database engine (DAO/ODBC/Others): These queries can't refer to anything happening in the application, so can't use functions defined in VBA nor form-based parameters, but are affected by transactions if the connection/workspace they operate on starts one.

If we want to query what's happening inside a transaction, we need to be in the same scope. This means you need to rewrite your DLookUp to use recordsets.

pieces = CurrentDb.OpenRecordset("SELECT Incoming_Pieces FROM Inventory WHERE Code='MT-1-1000x1x1'")(0).Value

An even more minimal example to demonstrate this:

DAO.DBEngine.BeginTrans
CurrentDb.Execute "INSERT INTO Table1(Field1) VALUES(1)"
Debug.Print CurrentDb.OpenRecordset("SELECT COUNT(*) FROM Table1")(0) 'Prints 1 assuming the table was empty
Debug.Print DCount("*", "Table1") 'Prints 0
DAO.DBEngine.Rollback