ADOX Table names - SQL - Multiple Tables 1

[MattSTech]

I am having a bit of trouble navigating ADOX with multiple catalogs in SQL.

The following code will pull the tables into one list and the fields into another. The problem is the SQL server has 5 different databases in it and this is pulling them all. Can I get this broken down by each DB in the server?
Matt

Private Sub Command1_Click()
    Dim myConnection As Connection
    Dim myCatalog As Catalog
    Dim myTable As Table
    Dim myColumn As Column
    
    Set myConnection = New Connection
    myConnection.ConnectionString = strMainConn
    
    myConnection.Open
    Set myCatalog = New Catalog
    Set myCatalog.ActiveConnection = myConnection
    
    For Each myTable In myCatalog.Tables
        If myTable.Type = "TABLE" Then
            List1.AddItem myTable.Name & ", " & myTable.Type
            For Each myColumn In myTable.Columns
                List2.AddItem " " & myColumn.Name
            Next
        End If
    Next
    
    Set myCatalog = Nothing
    myConnection.Close
    Set myConnection = Nothing
End Sub

 

[vb5prgrmr]

I wonder if you connect to a single database at a time if that will make a difference?



Good Luck

 

[MattSTech]

I tried setting the database name within the ADOX stuff, but it lead me in the wrong direction. Setting the initial catalog within the connection string, to the database I want, gave me the results I was looking for. Thanks for pushing me in the right direction!

Here is the connection for those who run into the same problem.

    strMainConn = "Provider=SQLOLEDB.1;Persist" & _
                    "Security Info=False;User Id=sa;Password=1234567;Initial Catalog=MyTableName;Data Source=Server01\SQLEXPRESS"

 

[gmmastros]

Whenever I need to get the table names and/or the column names from a SQL server database, I use the information_schema views.

Select * from information_schema.tables

select * from information_schema.columns

simply connect to the database you want and get the data like you would with any other recordset.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

My earlier advice is slightly misleading. I apologize. Information_Schema.Tables includes information regarding your tables AND views. You can easily remove the views like this...

Select * 
from   information_schema.tables 
Where  Table_Type = 'Base Table' 
Order By Table_Name

For the columns, you'll probably want to join to the previous query to remove the views, like this...

select * 
from   information_schema.columns As Cols
       Inner Join Information_Schema.Tables As Tabs
         On Cols.Table_Name = Tabs.Table_Name
         And Cols.Table_Schema = Tabs.Table_Schema
Where  Tabs.Table_Type = 'Base Table'
Order By Cols.Table_Name, Cols.Ordinal_Position

Notice that you will be able to get a lot of information from this last query. You should replace the * with the columns that you actually plan on using. This will save on bandwidth and ultimately improve performance (albeit slightly). From that last query, you can also get the data type, whether the column can contain nulls, default values for the column, etc...

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom