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

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

Private Sub Command2_Click()

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

   cat2.ActiveConnection = "Provider=MSDAORA;" & _
       "Data Source=dseoracle8i;User Id=<username>;password=<strong password>;"
   cat2.Tables.Delete "SCOTT.ADOX_TAB"

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):
    
    
    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