INFO: Ideas to Consider When Using Transactions Visual Basic

Last reviewed: April 7, 1997
Article ID: Q145757

The information in this article applies to:

Professional and Enterprise Editions of Microsoft Visual Basic for Windows, 16-bit only, 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.

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:

   ARTICLE-ID: Q103807
   TITLE     : 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."

Keywords : APrgOther kbusage vb416 vb4win kbinfo
Version : 4.0
Platform : NT WINDOWS
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 7, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.