[AccessD] Re: Transaction in MS-Access INFO REQUIRED

Robert L. Stewart rl_stewart at highstream.net
Tue Mar 16 09:37:58 CST 2004


Actually, there is.  But, it is limited to the scope of the transaction and 
not based on a log file.  SQL Server has both, transaction based 
ROLLBACK/COMMIT capability and the capability to go back to a log and 
rebuild transaction based on entries into it.

 From MS Access Help:

BeginTrans, CommitTrans, Rollback Methods Example
This example changes the job title of all sales representatives in the 
Employees table of the database. After the BeginTrans method starts a 
transaction that isolates all the changes made to the Employees table, the 
CommitTrans method saves the changes. Notice that you can use the Rollback 
method to undo changes that you saved using the Update method. Furthermore, 
the main transaction is nested within another transaction that 
automatically rolls back any changes made by the user during this example.
One or more table pages remain locked while the user decides whether or not 
to accept the changes. For this reason, this technique isn't recommended 
but shown only as an example.
Sub BeginTransX()

     Dim strName As String
     Dim strMessage As String
     Dim wrkDefault As Workspace
     Dim dbsNorthwind As Database
     Dim rstEmployees As Recordset

     ' Get default Workspace.
     Set wrkDefault = DBEngine.Workspaces(0)
     Set dbsNorthwind = OpenDatabase("Northwind.mdb")
     Set rstEmployees = _
         dbsNorthwind.OpenRecordset("Employees")

     ' Start of outer transaction.
     wrkDefault.BeginTrans
     ' Start of main transaction.
     wrkDefault.BeginTrans

     With rstEmployees

         ' Loop through recordset and ask user if she wants to
         ' change the title for a specified employee.
         Do Until .EOF
             If !Title = "Sales Representative" Then
                 strName = !LastName & ", " & !FirstName
                 strMessage = "Employee: " & strName & vbCr & _
                     "Change title to Account Executive?"

                 ' Change the title for the specified employee.
                 If MsgBox(strMessage, vbYesNo) = vbYes Then
                     .Edit
                     !Title = "Account Executive"
                     .Update
                 End If
             End If

             .MoveNext
         Loop

         ' Ask if the user wants to commit to all the changes
         ' made above.
         If MsgBox("Save all changes?", vbYesNo) = vbYes Then
             wrkDefault.CommitTrans
         Else
             wrkDefault.Rollback
         End If

         ' Print current data in recordset.
         .MoveFirst
         Do While Not .EOF
             Debug.Print !LastName & ", " & !FirstName & _
                 " - " & !Title
             .MoveNext
         Loop

         ' Roll back any changes made by the user since this is
         ' a demonstration.
         wrkDefault.Rollback
         .Close
     End With

     dbsNorthwind.Close

End Sub


BeginTrans, CommitTrans, Rollback Methods
The transaction methods manage transaction processing during a session 
defined by a Workspace object as follows:
·       BeginTrans begins a new transaction.

·       CommitTrans ends the ·  current transaction and saves the changes.

·       Rollback ends the current transaction and restores the databases in 
the Workspace object to the state they were in when the current transaction 
began.
Syntax
workspace.BeginTrans | CommitTrans [dbForceOSFlush] | Rollback
The workspace placeholder is an object variable that represents the 
Workspace containing the databases that will use transactions.
Remarks
You use these methods with a Workspace object when you want to treat a 
series of changes made to the databases in a session as one unit.
Typically, you use transactions to maintain the integrity of your data when 
you must both update records in two or more tables and ensure changes are 
completed (committed) in all tables or none at all (rolled back). For 
example, if you transfer money from one account to another, you might 
subtract an amount from one and add the amount to another. If either update 
fails, the accounts no longer balance. Use the BeginTrans method before 
updating the first record, and then, if any subsequent update fails, you 
can use the Rollback method to undo all of the updates. Use the CommitTrans 
method after you successfully update the last record.
In a Microsoft Jet workspace, you can include the dbForceOSFlush constant 
with CommitTrans. This forces the database engine to immediately flush all 
updates to disk, instead of caching them temporarily. Without using this 
option, a user could get control back immediately after the application 
program calls CommitTrans, turn the computer off, and not have the data 
written to disk. While using this option may affect your application’s 
performance, it is useful in situations where the computer could be shut 
off before cached updates are saved to disk.
Caution Within one Workspace object, transactions are always global to the 
Workspace and aren't limited to only one Connection or Database object. If 
you perform operations on more than one connection or database within a 
Workspace transaction, resolving the transaction (that is, using the 
CommitTrans or Rollback method) affects all operations on all connections 
and databases within that workspace.
After you use CommitTrans, you can't undo changes made during that 
transaction unless the transaction is nested within another transaction 
that is itself rolled back. If you nest transactions, you must resolve the 
current transaction before you can resolve a transaction at a higher level 
of nesting.
If you want to have simultaneous transactions with overlapping, non-nested 
scopes, you can create additional Workspace objects to contain the 
concurrent transactions.
If you close a Workspace object without resolving any pending transactions, 
the transactions are automatically rolled back.
If you use the CommitTrans or Rollback method without first using the 
BeginTrans method, an error occurs.
Some ISAM databases used in a Microsoft Jet workspace may not support 
transactions, in which case the Transactions property of the Database 
object or Recordset object is False. To make sure the database supports 
transactions, check the value of the Transactions property of the Database 
object before using the BeginTrans method. If you are using a Recordset 
object based on more than one database, check the Transactions property of 
the Recordset object. If a Recordset is based entirely on Microsoft Jet 
tables, you can always use transactions. Recordset objects based on tables 
created by other database products, however, may not support transactions. 
For example, you can't use transactions in a Recordset based on a Paradox 
table. In this case, the Transactions property is False. If the Database or 
Recordset doesn't support transactions, the methods are ignored and no 
error occurs.
You can't nest transactions if you are accessing ODBC data sources through 
the Microsoft Jet database engine.
In ODBC workspaces, when you use CommitTrans your cursor may no longer be 
valid. Use the Requery method to view the changes in the Recordset, or 
close and re-open the Recordset.
Notes
·       You can often improve the performance of your application by 
breaking operations that require disk access into transaction blocks. This 
buffers your operations and may significantly reduce the number of times a 
disk is accessed.

·       In a Microsoft Jet workspace, transactions are logged in a file 
kept in the directory specified by the TEMP environment variable on the 
workstation. If the transaction log file exhausts the available storage on 
your TEMP drive, the database engine triggers a run-time error. At this 
point, if you use CommitTrans, an indeterminate number of operations are 
committed, but the remaining uncompleted operations are lost, and the 
operation has to be restarted. Using a Rollback method releases the 
transaction log and rolls back all operations in the transaction.

·       Closing a clone Recordset within a pending transaction will cause 
an implicit Rollback operation.



BeginTrans, CommitTrans, and RollbackTrans Methods
These transaction methods manage transaction processing within a Connection 
object as follows:
·       BeginTrans — Begins a new transaction.

·       CommitTrans — Saves any changes and ends the current transaction. 
It may also start a new transaction.

·       RollbackTrans — Cancels any changes made during the current 
transaction and ends the transaction. It may also start a new transaction.
Syntax
level = object.BeginTrans()
object.BeginTrans
object.CommitTrans
object.RollbackTrans
Return Value
BeginTrans can be called as a function that returns a Long variable 
indicating the nesting level of the transaction.
Parameters
object A Connection object.
Connection
Use these methods with a Connection object when you want to save or cancel 
a series of changes made to the source data as a single unit. For example, 
to transfer money between accounts, you subtract an amount from one and add 
the same amount to the other. If either update fails, the accounts no 
longer balance. Making these changes within an open transaction ensures 
that either all or none of the changes go through.
Note Not all providers support transactions. Verify that the 
provider-defined property "Transaction DDL" appears in the Connection 
object's Properties collection, indicating that the provider supports 
transactions. If the provider does not support transactions, calling one of 
these methods will return an error.
After you call the BeginTrans method, the provider will no longer 
instantaneously commit changes you make until you call CommitTrans or 
RollbackTrans to end the transaction.
For providers that support nested transactions, calling the BeginTrans 
method within an open transaction starts a new, nested transaction. The 
return value indicates the level of nesting: a return value of "1" 
indicates you have opened a top-level transaction (that is, the transaction 
is not nested within another transaction), "2" indicates that you have 
opened a second-level transaction (a transaction nested within a top-level 
transaction), and so forth. Calling CommitTrans or RollbackTrans affects 
only the most recently opened transaction; you must close or roll back the 
current transaction before you can resolve any higher-level transactions.
Calling the CommitTrans method saves changes made within an open 
transaction on the connection and ends the transaction. Calling the 
RollbackTrans method reverses any changes made within an open transaction 
and ends the transaction. Calling either method when there is no open 
transaction generates an error.
Depending on the Connection object's Attributes property, calling either 
the CommitTrans or RollbackTrans methods may automatically start a new 
transaction. If the Attributes property is set to adXactCommitRetaining, 
the provider automatically starts a new transaction after a CommitTrans 
call. If the Attributes property is set to adXactAbortRetaining, the 
provider automatically starts a new transaction after a RollbackTrans call.


At 09:04 AM 3/16/2004 -0600, you wrote:
>Date: Tue, 16 Mar 2004 09:11:12 -0500
>From: "Jim Dettman" <jimdettman at earthlink.net>
>Subject: RE: [AccessD] Transaction in MS-Access INFO REQUIRED
>To: "Access Developers discussion and problem solving"
>         <accessd at databaseadvisors.com>
>Message-ID: <NEBBKADGELICHEJJCKGKGEBLIMAA.jimdettman at earthlink.net>
>Content-Type: text/plain;       charset="iso-8859-1"
>
>Sander/John,
>
>   Actually, to be a little more specific, there is no rollback/forward
>capability with JET as the database engine, but if you use Access with MSDE
>or SQL Server, then you pick that up.
>
>Jim Dettman
>Online Computer Services of WNY, Inc.
>(315) 699-3443
>jimdettman at earthlink.net





More information about the AccessD mailing list