PRB: "No Such Interface Supported" Error Calling ADOX Method

ID: Q198535

The information in this article applies to:

ActiveX Data Objects (ADO), version 2.1

SYMPTOMS

When attempting to add a Foreign Key to an SQL Server table, the following error is generated:

   Run time error '-2147467262 (800004002)':
   No Such Interface Supported.

Or, when trying to create an SQL Server Database, the following error is generated:

   Run time error '-2147467263 (800004001)'
   Not implemented

CAUSE

This is a current limitation of the OLE DB Provider for Microsoft ODBC and the OLE DB Provider for Microsoft SQL Server. Please see the ADOX Readme for more information on current limitations of the Microsoft OLE DB Providers or the documentation for the provider being used.

STATUS

This behavior is by design.

MORE INFORMATION

Other errors may occur with different interfaces or different OLE DB Providers.

Steps to Reproduce Behavior

1. Create a Standard EXE project in Visual Basic. Form1 is created by

   default.

2. Set a project reference to each of the following:

      "Microsoft ADO Ext. 2.1 for DDL and Security"
      "Microsoft ActiveX Data Objects 2.1 Library"

3. Paste the following code into the default Load method of the form:

      Dim cnn As ADODB.Connection
      Dim cat As ADOX.Catalog
      Dim tbl As ADOX.Table
      Dim fky As ADOX.Key
      Dim idx As ADOX.Index

      Set cnn = CreateObject("ADODB.Connection")
      Set cat = CreateObject("ADOX.Catalog")
      Set fky = CreateObject("ADOX.Key")
      Set tbl = CreateObject("ADOX.Table")
      Set idx = CreateObject("ADOX.Index")

      'Uncomment the following line to see "Not Implemented" error.
      'cat.Create "Provider=SQLOLEDB;Data Source=matthofa_nt;"

      With cnn
         .ConnectionString = "PROVIDER=sqloledb;" & _
                             "DATA SOURCE=your_server;" & _
                             "USER ID=sa;PASSWORD=;" & _
                             "INITIAL CATALOG=pubs"
         .Open
      End With

      Set cat.ActiveConnection = cnn

      'Create a Parent table.
      With tbl
         .Name = "tbl_1"
         .Columns.Append "Col_1", adInteger
         .Columns.Append "Col_2", adVarChar, 10
      End With

      'Create a Primary Key on the Parent table.
      With idx
         .Clustered = False
         .Columns.Append "Col_1"
         .Name = "tbl_1_pk"
         .PrimaryKey = True
      End With

      'Add the Index to the table's Indexes collection.
      tbl.Indexes.Append idx
      'Actually create the table and index on the server.
      cat.Tables.Append tbl

      Set tbl = Nothing
      Set tbl = CreateObject("ADOX.Table")

      'Create a Child table.
      With tbl
         .Name = "tbl_2"
         .Columns.Append "Col_1", adInteger
         .Columns.Append "Col_2", adVarChar, 10
      End With

      'Create the Foreign Key.
      With fky
         .Columns.Append "Col_1"
         .Name = "fkey"
         .RelatedTable = "tbl_1"
         .Columns("Col_1").RelatedColumn = "Col_1"
         .Type = adKeyForeign
      End With

      'Add the Foreign key to the child table's Keys Collection.
      tbl.Keys.Append fky
      'Actually create the child table and Foreign Key on the Server.
      'This is the line that will generate the error :
      '"No such interface support"
      'Comment the previous line of code and the table will be
      'successfully created on the server, minus the Foreign Key.
      cat.Tables.Append tbl

4. Run the form.

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q198534
   TITLE     : INFO: ADOX Readme File Included with ADO 2.1 Components

Additional query words:

Keywords          : kberrmsg kbnokeyword kbADO210 kbOLEDB 
Version           : WINDOWS:2.1
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: January 8, 1999