Microsoft KB Archive/107363: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - ">" to ">")
 
(2 intermediate revisions by the same user not shown)
Line 91: Line 91:
<pre class="codesample">  Sub Form_Load ()
<pre class="codesample">  Sub Form_Load ()


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


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


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


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


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


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


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


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


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


Line 154: Line 154:
<li>Start the program or press the F5 key.</li></ol>
<li>Start the program or press the F5 key.</li></ol>


This program gives the incorrect error message &quot;ODBC-call failed&quot;, err=3146, when attempting to delete an index from the Access database. This message is misleading because the program uses no ODBC.<br />
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.<br />
<br />
<br />
To work around the problem, close the table before doing the Delete method.<br />
To work around the problem, close the table before doing the Delete method.<br />
<br />
<br />
'''NOTE''': If the first data definition language (DDL) operation is a Delete method, the Delete will fail with the error, &quot;Name not found in this collection.&quot; 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.
'''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.


</div>
</div>

Latest revision as of 16:44, 20 July 2020

Article ID: 107363

Article Last Modified on 1/8/2003



APPLIES TO

  • Microsoft Visual Basic 3.0 Professional Edition



This article was previously published under Q107363

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.

This message is misleading because the program uses no ODBC. This is error number 3146, returned by the Err function.

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


Additional query words: buglist3.00 3.00 erase remove how-to create fixlist4.00

Keywords: kbbug kbfix KB107363