Microsoft KB Archive/271483

= PRB: Limitations of Using ADOX with Providers Other than Microsoft Jet OLE DB Provider =

Article ID: 271483

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * 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 Q271483



SYMPTOMS
Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) is designed for use with the Microsoft Jet Database Engine. So, using ADOX with OLE DB providers other than the Microsoft Jet OLE DB Provider may cause unexpected behavior or incorrect results. The exact behavior is dependent on the nature of the database for which the provider is written. If a provider is accesses a database system whose model is totally different from that of Jet, the behavior of ADOX could be unpredictable (for example, Jet does not support the concepts of CATALOG or SCHEMA).

This article lists some of the known problems that may occur when you try to use ADOX with an OLE DB Provider other than the Microsoft Jet OLE DB Provider.



MORE INFORMATION
The following points are related to the ADOX functionality:

TABLES Collection

ADOX calls the OpenSchema method with adSchemaTables and no restrictions. As a result, the TABLES collection includes all the tables accessible to the current user (as specified in the connection string) regardless of database catalogs and schemas.

COLUMNS Collection

ADOX calls the OpenSchema method with adSchemaColumns with just a table name as a restriction. As a result, the COLUMNS collection includes all the columns of all the tables that match the table name passed as a restriction, which are accessible to the current user regardless of database catalogs and schemas.

Naming Convention

The only naming convention ADOX supports is [object_name]. As a result, there is no way to differentiate objects with the same name that are in different schemas or catalogs.

ADOX does not support the following naming conventions:
 * [database_name/catalog_name].[owner_name/schema_name].[object_name]

-or-


 * [owner_name/schema_name].[object_name]

The following sample fails unless a table is specified as &quot;ADOX_TAB&quot; without a schema or database owner prefixed.

Note You must change the User ID= value and the password = value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

Private Sub Command2_Click

Dim tbl As New Table Dim cat2 As New ADOX.Catalog

cat2.ActiveConnection = &quot;Provider=MSDAORA;&quot; & _ &quot;Data Source=dseoracle8i;User Id= ;password= ;&quot; cat2.Tables.Delete &quot;SCOTT.ADOX_TAB&quot;

End Sub All the preceding features create issues similar to:

Case-sensitivity

Because ADOX is designed for Jet, which is case-insensitive in nature, ADOX does not work correctly with database systems like Oracle that support case-sensitivity. ADOX always makes a case-insensitive search on the specified collection.

Count Property

When you try to obtain a count of columns by specifying a table name, ADOX returns a total count of all the columns of all the tables that match the specified table name regardless of the catalogs and schemas.

The following example illustrates the preceding behavior:

NOTE: Before you run the following code, you must create the required objects in your database:

 If testing with Microsoft OLE DB Provider for Oracle (MSDAORA):

  Create a table as follows in the 'DEMO' schema or user account: create table COLTEST(col1 int)   Now, create a table with the same name 'COLTEST' in another schema or user account, 'SYSTEM' for example: create table COLTEST(col1 int, col2 int, col3 int)   Now, grant permissions to the DEMO user as follows: grant SELECT on SYSTEM.COLTEST to DEMO   If testing with Microsoft OLE DB Provider for SQL Server (SQLOLEDB):

  Use this code first: create table pubs.demo.COLTEST(col1 int)

create table pubs.dbo.COLTEST(col1 int, col2 int, col3 int)

grant SELECT on dbo.COLTEST to DEMO </li>  Next, use this code:

Note You must change the User ID= value and the password = value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. Private Sub Command1_Click

Dim cat1 As New ADOX.Catalog Dim tbl1 As ADOX.Table Dim col1 As ADOX.Column Dim cnt As Integer

cat1.ActiveConnection = &quot;Provider=MSDAORA;&quot; & _ &quot;Data Source=dseoracle8i;User Id= ;password= ;&quot;

'To test with SQLOLEDB, comment out the preceding line and uncomment the following line: 'cat1.ActiveConnection = &quot;Provider=SQLOLEDB.1;&quot; & _ &quot;Data Source=SQLSERVER11;User Id= ;password= ;initial catalog=pubs&quot;

For Each tbl1 In cat1.Tables If tbl1.Type = &quot;TABLE&quot; Then If tbl1.Name = &quot;COLTEST&quot; Then cnt = tbl1.Columns.Count Debug.Print &quot;Column Count of &quot; &&quot;'&quot; & tbl1.Name &&quot;' = &quot; & cnt; End If      End If    Next tbl1

End Sub </li></ol> </li></ul>

The Count property may not include tables whose names are in mixed or lower alphabetical case because ADOX does not provide a mechanism to specify case-sensitivity. ADOX only passes the table name as it is to the provider. For example, consider an Oracle table colTEST that was created with a mixed case name. ADOX sends the table name as colTEST without enclosing it in double quotes, which causes the provider to treat it as a case-insensitive table.

Result in this case:

Column Count of 'COLTEST' = 4

Indexing By Name

Consider a scenario related to Oracle:

User 'SCOTT' has tables 'prod' and 'PROD'. User 'JOHN' has tables 'PRod' and 'PrOD'. Now, the result of referencing a table by name like Catalog.Tables(&quot;prod&quot;) is indeterminate. It depends on the order of tables in the collection. Whichever table ADOX finds first (using a case-insensitive search) is the one that is referenced.

Delete Method:

Consider two tables 'SALES' and 'sales' in an Oracle schema 'DEMO'. Now, the following sample may drop the 'SALES' or 'sales' table, whichever is found first in the TABLES collection as ADOX makes a case-insensitive search.

Note You must change the User ID= value and the password = value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

Private Sub Command1_Click

Dim tbl As New Table Dim cat2 As New ADOX.Catalog

'Open the catalog. cat2.ActiveConnection = &quot;Provider=sqloledb;&quot; & _ &quot;Data Source=jonnakuti9;User Id= ;password= ;initial catalog=pubs&quot; cat2.Tables.Delete &quot;sales&quot;

End Sub

<div class="references_section">