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

Apparently that is by design. Did you verify that SELECT returns updated value? Why do you even need UPDATE action? Why are you not just doing data entry of a transaction record? — June7, Jul 16 '20 at 19:57
Yes `SELECT` does return the updated value. This was just sample code. In my real solution. I have several SQL statements being run which need to be in a transaction. The 5th query in the transaction depends on values updated by the 1st query. There is a lot more logic to it in VBA, so it couldn't all be done with one query. — Michael, Jul 16 '20 at 20:00

score 2 · Accepted Answer · answered Jul 16 '20 at 20:47

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:

Application. These queries can make use of user-defined functions and can refer to form-based parameters (such as WHERE MyField = Forms!SomeForm!SomeControl).
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

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

1 Answers1