ACC97:Transaction in ODBCDirect Workspace Causes Corrupted Index

Last reviewed: January 15, 1998
Article ID: Q173163
The information in this article applies to:
  • Microsoft Access 97
  • Microsoft Visual Basic Standard and Enterprise Editions, 32-bit only, for Windows, version 4.0

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

A Visual Basic for Applications procedure that uses ODBCDirect to create and populate a table in another Microsoft Access database also creates corrupted unique indexes in that table. This behavior occurs if the procedure performs the following steps in sequence:

  1. BeginTrans on the ODBCDirect workspace

  2. Execute method on a connection to Create Table with Unique Indexes

  3. Execute method on the connection to Append records to that table

  4. Execute method on the connection to Update the newly added records

  5. CommitTrans on the ODBCDirect workspace

If you modify data in a field that is a unique index in the resulting table but is not a primary key, you receive the following error message when you try to commit the record:

   Reserved Error (-1601); there is no message for this error.

You are able to make changes to data in other fields.

This behavior occurs when the ODBC data source is a Microsoft Access 97 database or a Microsoft Access version 7.0 database.

This behavior also occurs if you use Remote Data Object (RDO) instead of ODBCDirect in the Enterprise Edition of Microsoft Visual Basic version 4.0.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.

RESOLUTION

If you have already created the table and are unable to enter data because unique indexes are corrupted, press ESC to cancel the changes to the record. Then compact the database. After you have compacted the database, you can make changes to the data in the unique index fields.

There are three methods to prevent the corruption from occurring.

Method 1

The recommended resolution is to use a Microsoft Jet workspace rather than an ODBCDirect workspace to create and modify the table. When you use an ODBCDirect workspace to modify a Microsoft Jet database, data access objects (DAO) loads the Microsoft Jet ODBC driver, which in turn loads Microsoft Jet. A procedure in which DAO creates and modifies the table directly without going through ODBC is more efficient.

Method 2

Commit the transaction after you have created the table but before you append the records, or after you have appended the records but before you modify them.

Method 3

If you must create the table, append the records and modify the records within a single transaction, create the unique indexes by executing an SQL statement that is separate from the SQL statement that creates the table. Note that you can create a primary key in the same SQL statement that creates the table; although other unique indexes become corrupted, a primary index does not become corrupted.

The following example contains a procedure that creates the table, appends the records, and modifies the records within a single transaction.

  1. Create a blank database in Microsoft Access version 7.0 or 97.

  2. Create an ODBC DSN whose data source is the database created in Step 1, and name it "TestRDO97" (without the quotation marks).

  3. Create a blank database in Microsoft Access 97.

  4. Create a module and type the following line in the Declarations section if it is not already there:

          Option Explicit
    

  5. Type the following procedure:

          Sub CreateIndexTrans (strTableName as string, strDSN as string)
             Dim Connection1 As Connection, ws As Workspace
             On Error GoTo Errorhandler
             Set ws = DBEngine.CreateWorkspace _
                ("TransAct", "Admin", "", dbUseODBC)
             Set Connection1 = ws.OpenConnection("Con1", _
               dbDriverCompleteRequired, , "ODBC;DSN=" & strDSN)
             ws.BeginTrans
                Connection1.Execute "CREATE TABLE " & strTableName & _
                   " (ID INTEGER constraint ID PRIMARY KEY, " & _
                   "LastName Text (50), FirstName Text (50), keyCode " & _
                   "Text (10), SSN Text (20))"
             '----------- Create Indexes ------------------
                Connection1.Execute "CREATE INDEX " & _
                   "idxLastName ON " & strTableName & " (LastName)"
                Connection1.Execute "CREATE UNIQUE INDEX " & _
                   "idxKeyCode ON " & strTableName & " (KeyCode)"
                Connection1.Execute "CREATE UNIQUE INDEX " & _
                   "idxSSN ON " & strTableName & " (SSN)"
             '---------------------------------------------
                Connection1.Execute "INSERT INTO " & strTableName & _
                   " (ID, FirstName, LastName, KeyCode, SSN) " & _
                   "Values (1, 'Bob', 'Wire', 'ABC','012-34-5678')"
                Connection1.Execute "UPDATE " & strTableName & _
                   " SET FirstName = 'Robert', LastName='Wires', " & _
                   "KeyCode='A1B1C1', SSN='987-65-4321' WHERE ID = 1"
             ws.CommitTrans
             Connection1.Close
             MsgBox strTableName & " created in other database."
    
             Exit_CreateIndexProblem:
                Exit Sub
    
             Errorhandler:
                MsgBox CStr(Err) & " " & Err.Description
                Resume Exit_CreateIndexProblem
    
          End Sub
    
    

  6. To run this subroutine, type the following line in the Debug window, and then press ENTER:

          CreateIndexTrans "tblIndexTrans", "TestRDO97"
    

  7. Open the database you created in Step 1.

  8. Open tblIndexTrans.

  9. Change the data in the keyCode field or in the SSN field. Note that you can successfully commit the record.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Problem

  1. Create a blank database in Microsoft Access version 7.0 or 97.

  2. Create an ODBC DSN whose data source is the database created in Step 1, and name it "TestRDO97" (without the quotation marks).

  3. Create a blank database in Microsoft Access 97.

  4. Create a module and type the following line in the Declarations section if it is not already there:

          Option Explicit
    

  5. Type the following procedure:

          Sub CreateIndexProblem(strTableName as string, strDSN as string)
             Dim Connection1 As Connection, ws As Workspace
             On Error GoTo Errorhandler
             Set ws = DBEngine.CreateWorkspace  _
                ("TransAct", "Admin", "", dbUseODBC)
             Set Connection1 = ws.OpenConnection("Con1", _
                dbDriverCompleteRequired, , "ODBC;DSN=" & strDSN)
             ws.BeginTrans
                Connection1.Execute "CREATE TABLE " & strTableName & _
                   " (ID INTEGER Constraint ID PRIMARY KEY, " & _
                   "LastName Text Constraint idxLastName NOT NULL, " & _
                   "FirstName Text, " & _
                   "keyCode Text Constraint idxKeyCode UNIQUE, " & _
                   "SSN Text Constraint idxSSN UNIQUE)"
                Connection1.Execute "INSERT INTO " & strTableName & _
                   " (ID, FirstName, LastName, KeyCode, SSN) " & _
                   "Values (1, 'Bob', 'Wire', 'ABC','012-34-5678')"
                Connection1.Execute "UPDATE " & strTableName & _
                   " SET FirstName = 'Robert', LastName='Wires', " &  _
                   "KeyCode='A1B1C1', " & _
                   "SSN='987-65-4321' WHERE ID = 1"
             ws.CommitTrans
             Connection1.Close
             MsgBox strTableName & " created in other database."
    
          Exit_CreateIndexProblem:
             Exit Sub
    
          Errorhandler:
             MsgBox CStr(Err) & " " & Err.Description
             Resume Exit_CreateIndexProblem
          End Sub
    
    

  6. To run this subroutine, type the following line in the Debug window, and then press ENTER:

           CreateIndexProblem "tblTransProblem", "TestRDO97"
    

  7. Open the database you created in Step 1.

  8. Open tblTransProblem.

  9. Change the data in the keyCode field or in the SSN field; then try to commit the record. Note that you receive the error described in the "Symptoms" section.

REFERENCES

For more information about ODBCDirect, search the Help Index for "ODBCDirect Workspaces."

For more information about setting up ODBC data sources, search the Help Index for "ODBC, setting up data sources."

For more information about the SQL command CREATE TABLE, search the Help Index for "CREATE TABLE Statement (Microsoft Jet SQL)."

For more information about the SQL command CREATE INDEX, search the Help Index for "CREATE INDEX Statement (Microsoft Jet SQL)."

For more information about the SQL command INSERT INTO, search the Help Index for "INSERT INTO Statement (Microsoft Jet SQL)."


Additional query words: pra
Keywords : kberrmsg OdbcOthr ODBCGen
Version : WINDOWS:4.0,97
Platform : WINDOWS
Issue type : kbbug
Solution Type : kbworkaround


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: January 15, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.