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

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

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:

Q198534 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