I've a problem with the ADOX.Tables collection when connected to an Oracle
database through the OLEDB provider for ODBC for the following VB code:

Dim cnnS As New ADODB.Connection
Dim catS As New ADOX.Catalog
Dim tblS As New ADOX.Table

cnnS.Open "Provider=MSDASQL;DSN=OraTest;UID=gdgtest;PWD=gdgtest;"
Set catS.ActiveConnection = cnnS

Set tblS = catS.Tables("MY_TABLE")
Dim cntCol

cntCol = tblS.Columns.Count                 '*** this line returns 100
columns ??? My table has only 10 columns !!!!

I guess the wrong columns count is due to the fact that I've have 10 Oracle
databases on the same Oracle server and each of them has a similar
"MY_TABLE" table.

Is there a way to tell ADOX to work on a specific database (schema) and not
on all of them ?

PS:
- this problem append whatever Oracle ODBC driver I use (Microsoft, Merant)
- I cannot use the OLEDB provider for Oracle has this code should interop
with several DBMS (sybase, ...) and I want to minimise platform specific
code.

Thanks if you can help me.

One chance would be Set tblS = catS.Tables("MY_SCHEMA.MY_TABLE") but it does
not work with Microsoft ODBC for Oracle or Microsoft OLEDB Provider for
Oracle.

For your info, when you call Set tblS = catS.Tables("X") internally they
retrieve ALL the tables of your database without filtering, and then they
search the item "X" in the cached collection. In the above case, you get an
error saying that the item is not found in the collection.

The select statement, which will horrify your DBA (because it retrieves the
possibly thousands of tablenames of your database), is the following:

select * from (select null table_qualifier, decode (o1.owner, 'PUBLIC',
chr(0), o1.owner) table_owner, o1.object_name table_name,
decode(o1.owner,'SYS',decode(o1.object_type,'TABLE','SYSTEM
TABLE','VIEW','SYSTEM
VIEW',o1.object_type),'SYSTEM',decode(o1.object_type,'TABLE','SYSTEM
TABLE','VIEW','SYSTEM VIEW',o1.object_type),o1.object_type) table_type, null
remarks from all_objects o1 where o1.object_type in ('TABLE', 'VIEW')union
select null table_qualifier, decode (o2.owner , 'PUBLIC', chr(0), o2.owner)
table_owner, o2.object_name table_name, 'SYNONYM' table_type, null remarks
from all_objects o2, all_objects o3, all_synonyms s where o2.object_type =
'SYNONYM' and o3.object_type in ('TABLE','VIEW') and o2.owner = s.owner and
o2.object_name = s.synonym_name and s.table_owner = o3.owner and
s.table_name = o3.object_name) tables where 1=1  order by 4,2,3

Definitely, ADOX was intended for Access database (with a small number of
tables), and although you can use it with other databases, you will get this
kind of problems.

Try instead ADO OpenSchema(adSchemaColumns, Array(Empty,
"MY_SCHEMA","MY_TABLE")) and count the records of the recordset. It is much
faster and accurate, and can be used with other databases also.

And using ADO, you can use indistinctly OLEDB Providers (such MSDAORA) or
ODBC Drivers (such Merant, Sybase,etc) as far as they comply with the
interfaces.

--
Best regards,

Carlos J. Quintero

Freeware "all-in-one" Add-In for VB6: TabIndex Assistant, Procedure Callers,
Find in All Projects, Close Windows, Review TabIndex, Review Collections,
Add Property or Function, Add Error Handler, Add Header Comment, Clear
Immediate Window, Project Statistics:
www.mztools.com