FIX: Incorrect VB Error When Delete Index on Open Table

ID: Q107363

3.00 WINDOWS kbprg kbbuglist

The information in this article applies to:

- Professional Edition of Microsoft Visual Basic for Windows, version 3.0

SYMPTOMS

If you attempt to delete an index on an open table, you correctly get an error but the message is incorrect.

The program example given in the More Information section gives the following incorrect error when attempting to delete an index from an open Microsoft Access table:

   ODBC-call failed.

CAUSE

The ODBC-call failed message is incorrect. The message should instead say the table is currently open and cannot be locked.

You cannot delete an index from a table if the table is Open. This is behavior is by design. You must be able to lock the table before you can delete an index. You cannot lock the table if the table is open by anyone.

WORKAROUND

Close the table before deleting an index. You may also need to use the Refresh method on the TableDefs collection before using the Delete method.

STATUS

Regarding the incorrect error message, Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This problem has been corrected in Visual Basic version 4.0.

All other behavior described in this article is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add the following code to the Form Load event:

   Sub Form_Load ()

      Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
      Dim db As database
      If Dir$("c:\t.mdb") <> "" Then Kill "c:\t.mdb"
      Set db = CreateDatabase("c:\t.mdb", DB_LANG_GENERAL)

      Dim f1 As New field
      Dim f2 As New field
      f1.Name = "field1"
      f1.Type = 3  ' integer
      f2.Name = "field2"
      f2.Type = 3  ' integer

      Dim td As New TableDef
      td.Name = "table1"
      td.Fields.Append f1
      td.Fields.Append f2

      Dim ix As New Index
      ix.Name = "index1"
      ix.Fields = "field1;field2"
      td.Indexes.Append ix

      ' create the table
      db.TableDefs.Append td

      ' add records to the table
      Dim tb As table
      Set tb = db.OpenTable("table1")
      tb.AddNew
      tb.Fields("field1").Value = 1
      tb.Fields("field2").Value = 2
      tb.Update
      tb.AddNew
      tb.Fields("field1").Value = 4
      tb.Fields("field2").Value = 5
      tb.Update
      tb.AddNew
      tb.Fields("field1").Value = 7
      tb.Fields("field2").Value = 8
      tb.Update

      tb.Index = "index1"
      tb.Seek "=", 4, 5
      Print tb.NoMatch
      Print tb.Fields("field1").Value

      ' Delete the index:
      Dim td2 As TableDef
      Set td2 = db.TableDefs("table1")

      ' The following line causes "ODBC-call failed" error message:
      td2.Indexes.Delete db.TableDefs("table1").Indexes("Index1").Name
      ' The workaround is to move this statement to after the table Close

      tb.Close
      ' Workaround: move the statement from above to here:
      ' td2.Indexes.Delete db.TableDefs("table1").Indexes("Index1").Name
      db.Close

   End Sub

This program gives the incorrect error message "ODBC-call failed", err=3146, when attempting to delete an index from the Access database. This message is misleading because the program uses no ODBC.

To work around the problem, close the table before doing the Delete method.

NOTE: If the first data definition language (DDL) operation is a Delete method, the Delete will fail with the error, "Name not found in this collection." This is a separate bug and is explained in another article in the Microsoft Knowledge Base. To work around this bug, execute the db.TableDefs.Refresh method before attempting a Delete.

Additional reference words: buglist3.00 3.00 erase remove how-to create fixlist4.00 KBCategory: kbprg kbbuglist KBSubcategory: APrgDataOther

Keywords          :  kbbuglist
Version           : 3.00
Platform          : WINDOWS
Solution Type     : kbfix

Last Reviewed: November 2, 1997