How can I list the views of a database with a DSN connection?

Question

First, I want to show you the code I'm working on (VB6):

Dim db as Connection
Dim rs as Recordset
Dim rs1 as Recordset

db.Open "DSN=Oracle 10g; Uid=myUser; Pwd=myPassword;" 'I also tested SQLServer2005

'I connect successfully

Set rs = db.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
' Everything ok here. I can list Database tables

Set rs1 = db.OpenSchema(adSchemaViews)  'This is the line I have problems with

when I try to list the Database's views I got an error that says: "El proveedor no puede ejecutar la operaciòn requerida" ("Provider can't execute the required operation")

My SQLServer configuration accepts remote and local connection, and works with Windows Authentication. I configured an user for my Oracle Database that can access it with a password. When I connect to the same Databases using a connection string I can list both tables and views in both Oracle and SqlServer.

Am I missing something? A configuration option in the databases Engines. maybe?

Answer 1

I Found it! From a DSN connection, both Views and Tables are considerer as Tables.

This is the code that works:

Set rs1 = db.OpenSchema(adSchemaTables)
If rs1("TABLE_TYPE").Value = "VIEW" Then
' Do a lot of things with the views :D
End If

I hope this can help somebody.

Thanks for reading!