Visual Basic Q&A

Symptoms
ADOX is an extension to ActiveX Data Objects that allows the manipulation of the database schema. This article illustrates how to use ADOX to create a table and add a Primary Key.
Resolution
NOTE: Not all OLE DB providers support the interfaces required to support ADOX methods. With those providers, you have to use Data Definition Queries or another object model to manipulate the database schema.
The first procedure in the example below creates a new table in an existing Microsoft Access database, creates a new field in that table, then creates a primary key index. When adding a single-field primary key, you do not need to use the ADOX Key object.
The second procedure utilizes the ADOX Key object to add a multiple field key to a table.
Steps to Create the Sample Application In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default. On the Project menu, select References to add the following type libraries:
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.1 for DDL and Security Add two Command buttons (Command1 and Command2) and the following code to the Form1:

Option ExplicitPrivate Sub Command1_Click()” This code adds a single-field Primary key’Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, objTable As ADOX.TableSet Cn = New ADODB.ConnectionSet Cat = New ADOX.CatalogSet objTable = New ADOX.Table’Open the connectionCn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb”‘Open the CatalogSet Cat.ActiveConnection = Cn’Create the tableobjTable.Name = “Test_Table”‘Create and Append a new field to the “Test_Table” Columns CollectionobjTable.Columns.Append “PrimaryKey_Field”, adInteger’Create and Append a new key. Note that we are merely passing’the “PimaryKey_Field” column as the source of the primary key. This’new Key will be Appended to the Keys Collection of “Test_Table”objTable.Keys.Append “PrimaryKey”, adKeyPrimary, “PrimaryKey_Field”‘Append the newly created table to the Tables CollectionCat.Tables.Append objTable’ clean up objectsSet objKey = NothingSet objTable = NothingSet Cat = NothingCn.CloseSet Cn = NothingEnd SubPrivate Sub Command2_Click()” This code adds a multi-field Primary Key’Dim Cn As ADODB.Connection, Cat As ADOX.CatalogDim objTable As ADOX.Table, objKey As ADOX.KeySet Cn = New ADODB.ConnectionSet Cat = New ADOX.CatalogSet objTable = New ADOX.TableSet objKey = New ADOX.KeyCn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb”Set Cat.ActiveConnection = CnobjTable.Name = “Test_Table2″objTable.Columns.Append “PrimaryKey_Field1″, adIntegerobjTable.Columns.Append “PrimaryKey_Field2″, adIntegerobjKey.Name = “PrimaryKey”objKey.Type = adKeyPrimaryobjKey.Columns.Append “PrimaryKey_Field1″objKey.Columns.Append “PrimaryKey_Field2″objTable.Keys.Append objKeyCat.Tables.Append objTable’ clean up objectsSet objKey = NothingSet objTable = NothingSet Cat = NothingCn.CloseSet Cn = NothingEnd Sub NOTE: You might have to adjust the connect string to point to a valid Jet database. Run the application and click the Command buttons. You can check the table definitions for Test_Table and TestTable2 in Microsoft Access 97, Microsoft Access 2000, or the Visual Basic Visual Data Manager add-in.

Symptoms
When you connect to SQL Server using the SQL Server OLE DB Provider, if you use ActiveX Data Objects Extensions for DDL and Security (ADOX), the ADOX Column object exposes six dynamic properties in its Properties collection: Autoincrement, Default, Fixed Length, Nullable, Primary Key, and Unique.
However, any attempt to read one of these properties on an existing Column in an existing Table generates the following error message:

Error 3251, “Object or provider is not capable…” You may also encounter problems when you try to set these properties on a new Column unless you follow specific steps, which are described in the “More Information” section.
Resolution
Because you cannot use ADOX to read these Column properties, you can use an ADO Connection or SQL Server Query Analyzer to run the equivalent T-SQL statements to query object properties; or you can use SQL Server Enterprise Manager to view the properties manually.