asked on

List of Table names

Hello Experts,

I'm trying to connect to various databases and I need to pull back the table names after I connect. I am able to identify the database type ( Oracle, DB2, MSSQL & Access) before i connect based on the driver name I capture in a variable. Once conncected, I need the appropriate SQL string to be sent to the database based on the database type.

What would be the best way to accomplish this.

PSEDUOCODE:

if driver.text = Oracle then
sqlstring=("SELECT .... FROM.....'")

if driver.text = IBM DB2 then
sqlstring=("SELECT .... FROM.....'")

if driver.text = Access then
sqlstring=("SELECT .... FROM.....'")

if driver.text =MSSQL then
sqlstring=("SELECT .... FROM.....'")

The snippet I use below works fine for an Oracle database.

oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
Dim DA As New Odbc.OdbcDataAdapter("SELECT table_name from Tabs ORDER BY Table_Name;", _ oODBCConnection)

Thanks in advance

Pandkyon

ASKER CERTIFIED SOLUTION

ChetOS82

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ChetOS82

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pandkyon

ASKER

ChetOS82:
I will try the two suggestions for SQL and Access. I 'm not quite sure how to use the "List Table" in an sql statement. could you elaborate?

thanks!

Pandkyon

ChetOS82

I am not sure either, just see what happens if you issue it.

pandkyon

ASKER

ChetOS82:, Your solutions plus the snippet below works for all the dbs I have access to. Points awarded. Thanks,
Pandkyon

Dim DA As New Odbc.OdbcDataAdapter("SELECT * FROM SYSIBM.SYSTABLES TAB;", oODBCConnection)

Open in new window