Quote:
> Mark, if you are looking for an example of using transactions and how to
> avoid the pitfalls, see:
> Archive: Move Records to Another Table
> at:
> http://users.bigpond.net.au/abrowne1/ser-37.html
Hmm good stuff that, but...
<quote>
4. CommitTrans or Rollback, even after an error. The default workspace is always open, so an unterminated transaction remains active
even after your procedure ends! And since Access supports multiple transactions, you can dig yourself in further and further. Error
handling is essential, with the rollback in the error recovery section. A flag indicating whether you have a transaction open is a
practical way to manage this.
</quote>
Hmmm... doesn't that fairly leap out at you to build a TransactionManager class to insure that the transactions _do_ get
terminated - no matter how deeply nested and no matter what whatspaces they might be open on?
Something like this: (*warning* the following is fresh, untested code!)
'* supports multiple workspaces and nested transactions *
*** Begin Class
'Class DAOTransactionManager
' The private collection used to hold the real data
Private m_DBTransactionsManager As Collection
Private Type OpenTransactionInfo
Key As String
Workspace As DAO.Workspace 'possible trouble-spot here? if so make it object
TransactionDepth As Long
WorkspaceName As String
Initialized As Boolean
End Type
Private Const DBTM_CommitTrans = 1
Private Const DBTM_RollbackTrans = 0
Private Sub Class_Initialize()
' explicit assignment is slightly faster than auto-instancing
Set m_DBTransactionsManager = New Collection
End Sub
Private Sub Class_Terminate()
' *** HERE we make the assumption that if any transacions have not
' termiated with a commit previously, something horrible went wrong,
' and now we have to rollback any/all remaining transactions
' starting with the deepest to the shallowest
' yes I know this may not be strictly necessary, but let's do it
' the right way anyhow.
Dim uOTI As OpenTransactionInfo, lTmp As Long
Do Until m_DBTransactionsManager.Count = 0
For Each uOTI In m_DBTransactionsManager
lTmp = TerminateTransaction(uOTI.Key, DBTM_RollbackTrans)
Next
Loop
Set m_DBTransactionsManager = Nothing
End Sub
' Add a new OpenTransactionInfo item to the collection
' and begin the transaciotn on the DAO workspace we're using
' returning the DEPTH of the transaction we've started
Public Function BeginTrans(oWorkspace As DAO.Workspace) As Long
Dim newOTI As OpenTransactionInfo
Dim strKey As String, MaxOTI As OpenTransactionInfo
Dim lDepth As Long, strMaxKey As String
On Error GoTo Thud
strMaxKey = GetMAXItem(oWorkspace.Name)
If strMaxKey = "" Then
lDepth = 1
Else
MaxOTI = m_DBTransactionsManager(strMaxKey)
lDepth = MaxOTI.TransactionDepth + 1
End If
MaxOTI = Null
strKey = oWorkspace.Name & "-(" & CStr(lDepth) & ")"
newOTI.Key = strKey
Set newOTI.Workspace = oWorkspace
newOTI.TransactionDepth = lDepth
newOTI.WorkspaceName = oWorkspace.Name
newOTI.Initialized = True
m_DBTransactionsManager.Add newOTI, strKey
oWorkspace.BeginTrans
Set oWorkspace = Nothing 'just to make sure we don't have refcount woes
BeginTrans = lDepth
ExitBeginTrans:
Exit Function
Thud:
BeginTrans = 0
Resume ExitBeginTrans
End Function
Public Function Rollback(oWorkspace As DAO.Workspace) As Long
Dim strKey As String
strKey = GetMAXItem(oWorkspace.Name)
Rollback = TerminateTransaction(strKey, DBTM_RollbackTrans)
End Function
Public Function CommitTrans(oWorkspace As DAO.Workspace) As Long
Dim strKey As String
strKey = GetMAXItem(oWorkspace.Name)
Rollback = TerminateTransaction(strKey, DBTM_CommitTrans)
End Function
Private Function TerminateTransaction(Key As String, _
Optional TermType As String = DBTM_RollbackTrans _
) As Long
Dim uOTI As OpenTransactionInfo
uOTI = m_DBTransactionsManager(Key)
If Key = GetMAXItem(uOTI.WorkspaceName) Then
If TermType = DBTM_RollbackTrans Then
uOTI.Workspace.Rollback
Else
uOTI.Workspace.CommitTrans
End If
m_DBTransactionsManager.Remove uOTI.Key
Set uOTI.Workspace = Nothing
TerminateTransaction = uOTI.TransactionDepth - 1
uOTI = Null
Else 'we do nothing here if we don't have the deepest transaction
TerminateTransaction = -1 'an arror code
End If
End Function
'Returns a number indication how many open transations are pending for the specified Workspace from the collection
Private Function GetMAXItem(WorkspaceName As String) As String
Dim mOTI As OpenTransactionInfo, mMaxOTI As OpenTransactionInfo
'On Error Resume Next
If m_DBTransactionsManager.Count > 0 Then
For Each mOTI In m_DBTransactionsManager
If mOTI.WorkspaceName = WorkspaceName Then
If mMaxOTI.Initialized Then
If mOTI.TransactionDepth > mMaxOTI.TransactionDepth Then
mMaxOTI = mOTI
End If
Else
mMaxOTI = mOTI
End If
End If
Next
GetMAXItem = mMaxOTI.Key
Else
GetMAXItem = ""
End If
End Function
*** End Class
In use this class would be look something like this:
main.bas:
Public TransMgr as DAOTransactionManager
sub Main()
dim oWs as Dao.Workspace
Set TransMgr = new DAOTransactionManager
<.... do stuff...including opening the databse and ...
set oWS = DAO.DBEngine.Workspaces(..), or equivilant...>
'need to do database transacions
TransMgr.BeginTrans oWs
<...do database I/O stuff...>
' (if the app rolls over and dies before it gets here,
'the TransMgr_Terminate event should automatically fire
'and rollback *all* the open transactions before exiting)
If successful, database I/O, then:
TransMgr.CommitTrans oWs
if not successful, then:
TransMgr.Rollback oWs
End Sub
...but if you use this kind of class, you must *never* directly invoke any
Workspace</DBEngine>.BeginTrans/.Rollback/.CommitTrans methods
('cause you'll probably hose the transaciton nesting count)!!
Q: DBEngine - vs- Workspace & transactions