Microsoft KB Archive/198535

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

Article ID: 198535

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q198535



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

Run time error '-2147467262 (800004002)':

No Such Interface Supported.

Alternatively, under ADO 2.6 and later, you may see the following error:

Run-time error '-2147217792(80040e80)': MatchType is invalid or the value is not supported by the provider.

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

Run time error '-2147467263 (800004001)'

Not implemented

NOTE: When using ADO 2.5, the "No Such Interface Supported" error occurs in both situations described above.



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.

As a workaround, you can add the Foreign Key to the table with an ALTER TABLE SQL statement. See the code in the "More Information" section for an example of how to do this.



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

Steps to Reproduce Behavior
 Create a Standard EXE project in Visual Basic. Form1 is created by default. From the Project menu, select References. Then, from the list of references, select the following:

"Microsoft ADO Ext. 2.x for DDL and Security"

"Microsoft ActiveX Data Objects 2.x Library"

  Paste the following code into the default Load method of Form1. Modify the ConnectionString to connect to your SQL Server. 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" or "No such interface supported" error. 'cat.Create "Provider=SQLOLEDB;Data Source=your_server;"

With cnn .ConnectionString = "PROVIDER=sqloledb;" & _ "DATA SOURCE=your_server;" & _ "USER ID= ;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

'To workaround the problem, you can add the Foreign Key via a SQL statement instead: 'cat.ActiveConnection.Execute "ALTER TABLE tbl_2 ADD CONSTRAINT Col_1 " & _ "FOREIGN KEY(Col_1) REFERENCES tbl_1(Col_1)"

 Run the form, and note the error that is raised on appending the table with the Foreign Key.

