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

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.

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 "ADOX_TAB" without a schema or database owner prefixed.

Note You must change the User ID=<username> value and the password =<strong 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.
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):
  
  
  1. Create a table as follows in the 'DEMO' schema or user account:

     create table COLTEST(col1 int)

  2. 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)

  3. 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):
  
  
  1. 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

  2. Next, use this code:
     
     Note You must change the User ID=<username> value and the password =<strong 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 = "Provider=MSDAORA;" & _
                  "Data Source=dseoracle8i;User Id=<username>;password=<strong password>;"
     
         'To test with SQLOLEDB, comment out the preceding line and uncomment the following line:
         'cat1.ActiveConnection = "Provider=SQLOLEDB.1;" & _
                  "Data Source=SQLSERVER11;User Id=<username>;password=<strong password>;initial catalog=pubs"
     
         For Each tbl1 In cat1.Tables
           If tbl1.Type = "TABLE" Then
            If tbl1.Name = "COLTEST" Then
               cnt = tbl1.Columns.Count
               Debug.Print "Column Count of " &"'" & tbl1.Name &"' = " & cnt;
              End If
            End If
         Next tbl1
     
     End Sub

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("prod") 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=<username> value and the password =<strong 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 = "Provider=sqloledb;" & _
          "Data Source=jonnakuti9;User Id=<username>;password=<strong password>;initial catalog=pubs"
     
     cat2.Tables.Delete "sales"
  
  End Sub

For additional information about ADOX, click the article number below to view the article in the Microsoft Knowledge Base: