FIX: Incorrect VB Error When Delete Index on Open Table

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
   						

3. Start the program or press the F5 key.

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.