INFO: Ideas to Consider When Using Transactions Visual Basic

ID: Q145757

The information in this article applies to:

Microsoft Visual Basic Professional and Enterprise Editions, 16-bit only, for Windows, version 4.0

SUMMARY

When you are using the transaction statements to perform the processing on your database records, you may want to structure the transaction processing under certain guidelines. For example, you should try to keep your transaction processing loop as small as possible. Do not try to execute a large number of statements before performing a Rollback or CommitTrans statement. Below is a brief list of ideas and possible problems that you may encounter if you do not structure your transaction processing loop under certain guidelines.

In later versions of Jet that come with Visual Basic 5.0 and 6.0, internal automatic transactions are provided for DAO. Refer to page 74 in the "Guide to Data Access Objects" manual for Visual Basic 5.0.

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:

Q103807 : How to Convert a Text File into a New Access Database

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."

REFERENCES

For more information on Transactions and DAO please see the following article in the Microsoft Knowledge Base:

Q170548 : PRB: DAO Transactions to ODBC Database Can Hang Application

Additional query words: kbVBp400 kbdse kbDSupport kbVBp

Keywords : kbGrpVBDB
Version :
Platform : NT WINDOWS
Issue type : kbinfo