ADO Transactions in Access 2000

[R. D. Bescherer, Jr.]

I have been having difficulty implementing transactions using ADO in Access 2000. After I execute the BeginTrans statement and change the items in the recordsets I get an error on CommitTrans that no transaction has been started. Here is a sample of the code that doesn't work:

Private Sub cmdDischarge_Click()
Dim lngI As Long, flagHold As Boolean
Dim rstBed As ADODB.Recordset
Dim rstBeds As ADODB.Recordset
Dim rstCase As ADODB.Recordset

lngI = MsgBox("Discharge patient """ & lblName.Caption & """ from the ICU?", _
vbOKCancel + vbQuestion + vbApplicationModal, _
"Discharge Patient?")
If lngI = vbOK Then
flagHold = False
' Query Bed.Active, Bed.RequireChange where BedKey = mlngOldBedKey
Set rstBed = New ADODB.Recordset
rstBed.Open "SELECT * FROM Bed WHERE BedKey = " & mlngOldBedKey, _
CurrentProject.Connection, adOpenDynamic, adLockPessimistic
' If RequireChange is True then set flag for deactivation of hold bed
If rstBed("RequireChange").Value = True Then
If rstBed("Bed").Value <> "*" Then
flagHold = True
End If
End If
Set rstBeds = New ADODB.Recordset
rstBeds.Open "SELECT * FROM Beds WHERE CaseKey = " & mlngCaseKey & " AND Active = True", _
CurrentProject.Connection, adOpenDynamic, adLockPessimistic
Set rstCase = New ADODB.Recordset
rstCase.Open "SELECT * FROM [Case] WHERE CaseKey = " & mlngCaseKey, _
CurrentProject.Connection, adOpenDynamic, adLockPessimistic
' Begin transaction
lngI = CurrentProject.Connection.BeginTrans
' Discharge Patient by setting Beds.Active to False
rstBeds("Active") = False
rstBeds.Update
' Discharge Case by setting Case.TransferDate to Now()
rstCase("TransferDate") = Now()
rstCase.Update
' Inactivate hold bed if flag set
If flagHold = True Then
rstBed("Active") = False
rstBed.Update
End If
' Commit transaction
CurrentProject.Connection.CommitTrans
rstBed.Close
rstBeds.Close
rstCase.Close
Set rstBed = Nothing
Set rstBeds = Nothing
Set rstCase = Nothing
End If
End Sub

If I comment out the BeginTrans and CommitTrans lines the code works as intended but without the benefit of being conducted in a transaction. Any suggestions?

Thanks!

Rudy Bescherer, Jr.

 

[Gary Miller]

Rudy,

I am not sure if this has to do with your problem or not,
but wouldn't it be more economical to move your BeginTrans
to a point before you bother opening your recordsets in case
you don't need them? You would then need to move the
CommitTrans down to after you close the recordsets.

Have you tried putting a break point in the code to step
through and see if anything unusual happens?

Gary Miller
Sisters, OR


message

I have been having difficulty implementing transactions

using ADO in Access 2000. After I execute the BeginTrans
statement and change the items in the recordsets I get an
error on CommitTrans that no transaction has been started.
Here is a sample of the code that doesn't work:

Private Sub cmdDischarge_Click()
Dim lngI As Long, flagHold As Boolean
Dim rstBed As ADODB.Recordset
Dim rstBeds As ADODB.Recordset
Dim rstCase As ADODB.Recordset

lngI = MsgBox("Discharge patient """ & lblName.Caption & """ from the ICU?", _
vbOKCancel + vbQuestion + vbApplicationModal, _
"Discharge Patient?")
If lngI = vbOK Then
flagHold = False
' Query Bed.Active, Bed.RequireChange where BedKey = mlngOldBedKey
Set rstBed = New ADODB.Recordset
rstBed.Open "SELECT * FROM Bed WHERE BedKey = " & mlngOldBedKey, _
CurrentProject.Connection, adOpenDynamic, adLockPessimistic
' If RequireChange is True then set flag for deactivation of hold bed
If rstBed("RequireChange").Value = True Then
If rstBed("Bed").Value <> "*" Then
flagHold = True
End If
End If
Set rstBeds = New ADODB.Recordset
rstBeds.Open "SELECT * FROM Beds WHERE CaseKey = "

& mlngCaseKey & " AND Active = True", _

CurrentProject.Connection, adOpenDynamic, adLockPessimistic
Set rstCase = New ADODB.Recordset
rstCase.Open "SELECT * FROM [Case] WHERE CaseKey = " & mlngCaseKey, _
CurrentProject.Connection, adOpenDynamic, adLockPessimistic
' Begin transaction
lngI = CurrentProject.Connection.BeginTrans
' Discharge Patient by setting Beds.Active to False
rstBeds("Active") = False
rstBeds.Update
' Discharge Case by setting Case.TransferDate to Now()
rstCase("TransferDate") = Now()
rstCase.Update
' Inactivate hold bed if flag set
If flagHold = True Then
rstBed("Active") = False
rstBed.Update
End If
' Commit transaction
CurrentProject.Connection.CommitTrans
rstBed.Close
rstBeds.Close
rstCase.Close
Set rstBed = Nothing
Set rstBeds = Nothing
Set rstCase = Nothing
End If
End Sub

If I comment out the BeginTrans and CommitTrans lines the

code works as intended but without the benefit of being
conducted in a transaction. Any suggestions?