Find Record Count during transaction (1 Viewer)

GBalcom

Much to learn!
Local time
Today, 16:31
Joined
Jun 7, 2012
Messages
459
Long story short, I'm using a DAO Transaction during my unit testing.

Apparently, Dcount doesn't pickup the newly inserted records in the middle of a transaction. If I comment out the transaction code, it works fine. Ideas? :banghead:


Code:
Private Sub AddEventRecordTestMethod()
    On Error GoTo TestFail
    
    Dim iCalRecCount As Integer
    Dim iLogRecCount As Integer
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    
    'setup transaction
    Set db = CurrentDb
    Set ws = DBEngine(0)
        
    'get existing record counts
    iCalRecCount = DCount("*", "tblCalendarEvents")
    iLogRecCount = DCount("*", "tblLog")
    
    'begin transaction
    ws.BeginTrans
    
    
    'Arrange:
    cal.ColorNumber = "7ae7bf"
    cal.Description = "TEST Description"
    cal.StartDate = Date
    cal.Summary = "TEST Summary"
    cal.WorkOrder = 0
        
    'Act:

    
    If cal.AddEvent Then
    
        Dim TestResult As Boolean
        Dim iCalNewCount As Integer
        Dim iLogNewCount As Integer
        
            iCalNewCount = Nz(DCount("*", "tblCalendarEvents"), 0)
            iLogNewCount = Nz(DCount("*", "tblLog"), 0)
        
            If (iCalRecCount + 1) = iCalNewCount And (iLogRecCount + 1) = iLogNewCount Then
                TestResult = True
            Else
                TestResult = False
            End If
            
    Else
        Assert.Fail "cal.AddEvent Failed"
        
    End If



    'Assert:
    Assert.IsTrue TestResult, "New records NOT added to tblCalendarEvents and tblLog"
    Assert.Succeed

TestExit:
    ws.Rollback
    Set ws = Nothing
    Set db = Nothing
    
    Exit Sub
TestFail:
    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description
    'ws.Rollback
    Set ws = Nothing
    
End Sub

cal is defined in this module, as a new clsCalendarEvent. Pertinent Code for clsCalendarEvent is below:

Code:
Private Sub AddEventRecord()
    ' Status:  In Devlopment
    ' Comments:
    ' Params  :
    ' Returns : Boolean
    ' Created : 05/21/19 10:16 GB
    ' Modified:
    
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim lngCalEventID As Long   'New Calendar Event ID, used to insert new record into tblLog for event
    

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblCalendarEvents", dbOpenDynaset, dbSeeChanges)

    With rst
        .AddNew
        .Fields("WorkOrderID") = Me.WorkOrder
        .Fields("Title") = Me.Summary
        .Fields("Body") = Me.Description
        .Fields("StartDate") = Me.StartDate
        .Fields("AllDayEvent") = True
        .Fields("CreateTime") = Now()
        .Update
        'pull newly created ID to log below
        lngCalEventID = .Fields("ID")
        .Close
    End With
    
    
    'change recordsets
    Set rst = dbs.OpenRecordset("tblLog", dbOpenDynaset, dbSeeChanges)
    
    With rst
        .AddNew
        .Fields("EventTypeID") = 1
        .Fields("WorkOrderID") = Me.WorkOrder
        .Fields("CalendarEventId") = lngCalEventID
        .Fields("Memo") = "Added Calendar Event"
        .Fields("TimeStamp") = Now()
        .Update
        .Close
    End With
    
    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Exit Sub

PROC_ERR:
    Err.Raise Err.Number
    Resume
    'TVCodeTools ErrorHandlerEnd

End Sub
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:31
Joined
Jan 23, 2006
Messages
15,364
Not sure I'm following. I can rationalize the situation as follows.
It would seem --thinking in terms of transaction logic -- that since the transaction has not been completed, you are in a type of "transition" state. From database view, I'm in the middle of something and must complete that something either successfully or unsuccessfully. You could get record count before and/or after the transaction, but not during the transaction.

That's a best guess on my part, and may not be correct.
 

Minty

AWF VIP
Local time
Today, 23:31
Joined
Jul 26, 2013
Messages
10,355
I'm not sure this isn't a typo

AddEventRecord

and

cal.AddEvent

?

Edit - and I agree with JDraw - it's not committed until the transaction is complete.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:31
Joined
Oct 29, 2018
Messages
21,358
Hmm. I must be blind. I see begintrans but don't see committrans. In any case, you get three votes on DCount() only looks at what's in the table. So, anything not yet in the table won't be included in the DCount() result.
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:31
Joined
Jan 23, 2006
Messages
15,364
DBGuy,
Good eye on the Commit. I tried to reason the logic, and didn't look at the code.
 

GBalcom

Much to learn!
Local time
Today, 16:31
Joined
Jun 7, 2012
Messages
459
Thanks for the quick responses guys.

As far as the transaction. I was trying to use it because I don't want the records created by this test to persist in the database. So, It doesn't look like it will work then, because once I commit a transaction (so I can see the record in the db), I cannot rollback the transaction (to remove them from the db). Correct?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:31
Joined
Oct 29, 2018
Messages
21,358
Thanks for the quick responses guys.

As far as the transaction. I was trying to use it because I don't want the records created by this test to persist in the database. So, It doesn't look like it will work then, because once I commit a transaction (so I can see the record in the db), I cannot rollback the transaction (to remove them from the db). Correct?

Correct! You use transactions to "test" out the data changes before committing them to the table. Once they're committed, then the only way to roll them back is to use a DELETE query, which if you're using identity or sequence fields, would lose them. So, perhaps, for testing purposes, you might try using a temp table instead of your real tables. Either way, please remember that any domain aggregate function can only "see" what's been stored in the table.


Cheers!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:31
Joined
Feb 28, 2001
Messages
27,001
To amplify DBG's absolutely correct statement, when using D-anything() functions, they imply a separate query through a private recordset (private to the function code of the domain aggregate). If a COMMIT has not occurred, the new records aren't there to be aggregated. So... they are a bunch of no-count records.

But what's to count? Your subroutines don't loop. They either add 1 record or they don't add anything. So a DCount of RST is the base count and the final count will either be the base or the base + 1 depending on failure or success. Or did I miss something?
 

Users who are viewing this thread

Top Bottom