• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Table List

Hi Experts

I have an active ADODB connection, which is failing to connect to a table within the catalog. Can I return a list of available tables in the catalog?


connection:

con.ConnectionString = "Provider=MSDASQL.1;" _
            & "Password=abcde;" _
            & "Persist Security Info=True;" _
            & "User ID=CustClient;" _
            & "Data Source=TransportDB_Comp1;" _
            & "Mode=ReadWrite;" _
            & "Initial(Catalog = Abacus_Comp1);" _
            & "MARS Connection=True;"

 con.Open()
Regards,
Joe
0
JoeBo747
Asked:
JoeBo747
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
check out the OpenSchema method of the connection object:
http://support.microsoft.com/kb/186246

0
 
DarrenDCommented:
0
 
JoeBo747Author Commented:
Hi Angelll DarrenD

Thanks for the replies, I have tried the advised method and can establish a connection using

con.Open()
            'Getting the information about all the tables.

            Dim criteria(3) As Object
            criteria(0) = "pubs"
            ' criteria(1) '= Empty
            ' criteria(2) '= Empty
            criteria(3) = "table"
            rst = con.OpenSchema(ADODB.SchemaEnum.adSchemaTables, criteria)
            While Not rst.EOF
                Debug.Print(rst.AbsolutePosition)  what do I use here in vb.net to return table names
                rst.MoveNext()
            End While

I have also tried the method example Darren has pointed to, I have not been able to return a recordset this way?

Regards,
Joe
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JoeBo747Author Commented:
Hi
Should have mentioned this.
rst!table_name results in Overload resolution failed because no accessible fields accept this number of arguments

regards
joe
0
 
JoeBo747Author Commented:
Hi Thanks for your support, I was able to discover my problem by using access too provide me with the table names from the  definitions  table.

Dim x As Integer, y As Integer
        x = 0
        Dim CN As New ADODB.Connection
        CN = New ADODB.Connection
       
            CN.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            CN.Open ("Provider=MSDASQL.1;Password=abcde;Persist Security Info=True;User ID=custclient;Data Source=TransportRecs;Mode=ReadWrite;Initial Catalog=TransportRecs")
            Dim SQL As String
            SQL = "select * from information_schema.tables "
            'creating recordset
            Dim rs As ADODB.Recordset
            Set rs = New ADODB.Recordset
            rs.Open SQL, CN
            rs.MoveFirst
            y = rs.RecordCount
            Do While x < y
            Debug.Print rs!table_name
            x = x + 1
            rs.MoveNext
            Loop
My lack of knowledge of SQL table structure was the problem. The field names on the server are all prefixed dbo_ but sql looks for the short name I was providing the full name.

Regards
oe
0
 
wsh2Commented:
As you are VB.Net you may want to try using this to retreive all the Table / Column names in your database:
----------------------------------------------------------------------------------------------------------------
Private Sub Display_Schema()
'
'  Initialize
'  CHANGE TO strCN TO YOUR CONNECTION STRING
   Dim strCN As String = _
      "Connect Timeout=30;" & _
      "Data Source=.\SQLEXPRESS;" & _
      "Integrated Security=True;" & _
      "User Instance=True;" & _
      "AttachDbFilename=" & _
      "'" & "C:\MsSQL_Express\TestData\NORTHWND.MDF" & "';"
   Dim strCMD As String = _
      "SELECT C.TABLE_NAME,                  " & _
      "       C.COLUMN_NAME,                 " & _
      "       T.TABLE_TYPE                   " & _
      "  FROM INFORMATION_SCHEMA.[COLUMNS] C " & _
      " INNER JOIN                           " & _
      "       INFORMATION_SCHEMA.[TABLES]  T " & _
      "    ON C.TABLE_NAME = T.TABLE_NAME    " & _
      " ORDER BY T.TABLE_TYPE,               " & _
      "       C.TABLE_NAME, C.COLUMN_NAME    " & _
      ";                                     "
'
'  Process
'  Display all database Tables / Column names in the
'  VS menubar -> Debug -> Windows -> Output window.
   Using adoCN As _
      New System.Data.SqlClient.SqlConnection(strCN)
      adoCN.Open()
      Using adoCMD As _
      New System.Data.SqlClient.SqlCommand(strCMD, adoCN)
         Dim oleDR As System.Data.SqlClient.SqlDataReader
         oleDR = adoCMD.ExecuteReader()
         With oleDR
            Do Until .Read() <> True
               Console.WriteLine( _
                  .Item("TABLE_TYPE").ToString & vbTab & _
                  .Item("TABLE_NAME").ToString & vbTab & _
                  .Item("COLUMN_NAME").ToString _
                  )
            Loop
            .Close()
         End With
      End Using
   End Using
'
End Sub
----------------------------------------------------------------------------------------------------------------
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now