Select the product you need help with

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

Article ID: 271483 - View products that this article applies to.

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.

Back to the top | Give Feedback

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

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

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

Back to the top | Give Feedback

REFERENCES

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

198534

(http://support.microsoft.com/kb/198534/EN-US/ )

INFO: ADOX Readme File Included with ADO 2.1 Components

272001

(http://support.microsoft.com/kb/272001/EN-US/ )

Attributes Property of ADOX Columns Collection May Cause Append Method to Fail

Back to the top | Give Feedback

Properties

Article ID: 271483 - Last Review: October 31, 2003 - Revision: 2.0

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

kbcodesnippet kbdatabase kboracle kbprb KB271483

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top | Give Feedback