INFO: Ideas to Consider When Using Transactions Visual Basic (145757)

MORE INFORMATION

The list below is provided to help you produce better and more reliable transaction type processing programs with the transaction statements provided in Visual Basic 4.0 for Windows.

List of ideas to keep in mind with Transaction Processing

• Keep the transaction processing loops as short as possible. For example:

       BeginTrans
        Statement1....
        Statement2....
        Statement3....
        Statement4.... '*** If you have a number of statements, you could
                       '*** slow processing in a multi-user system, or
                       '*** increase the chance of an error occurring.
  
       CommitTrans  (or Rollback)
   
  						

• When you are working with transaction statements, it is not recommended to proceed onto another form or load another form in the transaction processing loop. For example:

        BeginTrans
           Statement1....
           form2.Show  or form2.Show 1 or Load form2  '* Not recommended.
           Statement3....
           Statement4....
        CommitTrans  (or  Rollback)
  
  						

• When you are working with Data controls, it is not recommended to proceed onto another form in the transaction processing loop. For example:

        BeginTrans
           Statement1....
           form2.Show or Load form2    '*** Not recommended, may cause error
                                       '*** 3034 'Commit or Rollback without
                                       '*** BeginTrans' to occur.
           Statement3....
           Statement4....
        CommitTrans  (or  Rollback)
  
  						

• When you are working with data controls, it is not recommended to perform a Refresh method in the transaction processing loop. For example:

        BeginTrans
           Statement1....
           Data1.Refresh         '*** Not recommended, may cause error 3034
                                 '*** 'Commit or Rollback without BeginTrans'
           Statement3....        '*** to occur.
           Statement4....
        CommitTrans  (or  Rollback)
  
  						

• When you are working with Object variables, it is not recommended to perform a Dim of an Object variable in the transaction processing loop. For example:

        BeginTrans
           Dim db As Database                   '*** Not recommended.
           Dim ds As Dynaset                    '*** Not recommended.
           Dim tb As Table                      '*** Not recommended.
           Dim sn As Snapshot                   '*** Not recommended.
           Dim qy As Querydef                   '*** Not recommended.
           Statement1....
           Statement2....
        CommitTrans  (or  Rollback)
  
  						

• When you are working with Object variables, it is not recommended to perform a Open or Close method or function in the transaction processing loop. For example:

        BeginTrans
           Statement1....
           Set db = Opendatabase("Biblio.MDB")   '*** Not recommended.
           Set ds = Opentable("Authors")         '*** Not recommended.
           ds.Close                              '*** Not recommended.
           db.Close                              '*** Not recommended.
           Statement6....
           Statement7....
        CommitTrans  (or  Rollback)
  
  						

• When you are working with Object variables, it is not recommended to perform a Create... method or function in the transaction processing loop. For example:

        BeginTrans
           Statement1....   '*** The next line of code is not recommended.
           Set db = CreateDatabase("Mydb.MDB", DB_LANG_GENERAL, False)
           Set ds = db.CreateDynaset("Titles")      '*** Not recommended.
           Set sn = db.CreateSnapshot("Publishers") '*** Not recommended.
           Set qy = db.CreateQueryDef("Query1", "Select * From Authors;")
                       '*** The previous line of code is not recommended.
           Statement6....
           Statement7....
        CommitTrans  (or  Rollback)
  
  						

A recommended example of using the transaction functions is provided below. This example demonstrates how to convert an ASCII text file into a Access database table. With the transaction statements added, you should see a improvement in speed. For a complete example of the entire article, please see the following article in the Microsoft Knowledge Base:

For example:

   BeginTrans
       Do While Not (EOF(1))
          newtb.AddNew
          Line Input #1, tmp1$               '*** Retrieves empl_id.
          Line Input #1, tmp2$               '*** Retrieves empl_name.
          Line Input #1, tmp3$               '*** Retrieves empl_addr.
          Line Input #1, tmp4$               '*** Retrieves empl_ssn.
          newtb("Emp_ID") = Trim$(tmp1$)     '*** Place in new field1.
          newtb("Emp_Name") = Trim$(tmp2$)   '*** Place in new field2.
          newtb("Emp_Addr") = Trim$(tmp3$)   '*** Place in new field3.
          newtb("Emp_SSN") = Trim$(tmp4$)    '*** Place in new field4.
          newtb.Update                       '*** Saving to new table.
       Loop
   CommitTrans
				

Note: For more information on Visual Basic and transactions, see the Database Transaction Methods and Statements section of the Optimization and Version Compatibility Considerations chapter in the "Guide to Data Access Objects" book (contained in the "Professional Features" Visual Basic manual).

The Visual Basic manual assumes you are using the 32-bit edition of Visual Basic 4.0. If you are using the 16-bit edition of Visual Basic 4.0, refer to the Microsoft Access 2.0 user manual. For general information on transaction processing systems, refer to the "Microsoft SQL Server Transact SQL Guide."