none
Get the list of Fields in Table in Database of the ms access in vb.net 2008 RRS feed

  • Question

  • I'm using vb2008 with ms access 2007 formated the DB file by 2003 mdb

    I found this code in this link: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/d2eaf851-fc06-49a1-b7bd-bca76669783e/

    . is to get the list of tables in DB Ms access.

    I used the code as it showed below with my DB access 2003 and it worked well.

     

    I wonder to know how can create code to get the list of fields(columns) in table in same DB?

     

    Dim userTables As DataTable = Nothing
            Dim restrictions() As String = New String(3) {}
            restrictions(3) = "Table"
            conn.Open()
            ' Get list of user tables
            userTables = conn.GetSchema("Tables", restrictions)
            conn.Close()
            ' Add list of table names to listBox
            Dim i As Integer
            For i = 0 To userTables.Rows.Count - 1 Step i + 1
                'MsgBox(userTables.Rows(i)(2).ToString())
                Form1.ListBox1.Items.Add(userTables.Rows(i)(2).ToString())
            Next
    
    
    

     


    • Edited by bhd9 Tuesday, January 24, 2012 11:34 PM
    Tuesday, January 24, 2012 11:31 PM

Answers

  • The below sample code should help. You will just need to change the connection string to used the ACE Provider:

            Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=C:\Test Files\db1 XP.mdb;"
            Dim Connection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            Connection.Open()
            Dim Restrictions() As String = {Nothing, Nothing, "Table1", Nothing}
            Dim CollectionName As String = "Columns"
            Dim dt As DataTable = Connection.GetSchema(CollectionName, Restrictions)
            For Each TableRow As DataRow In dt.Rows
                Console.WriteLine(TableRow.Item("COLUMN_NAME").ToString)
            Next
    
            DataGridView1.DataSource = dt
            Connection.Close()
    
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by bhd9 Thursday, January 26, 2012 7:13 PM
    Thursday, January 26, 2012 6:29 PM

All replies

  • It seems a vb.net problem
    Wednesday, January 25, 2012 7:31 AM
  • It seems a vb.net problem
    What problem?  the code above succeed  with me to get the list table. but I'm for Fields I don't know how.

    • Edited by bhd9 Wednesday, January 25, 2012 12:25 PM
    Wednesday, January 25, 2012 12:24 PM
  • The below sample code should help. You will just need to change the connection string to used the ACE Provider:

            Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=C:\Test Files\db1 XP.mdb;"
            Dim Connection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            Connection.Open()
            Dim Restrictions() As String = {Nothing, Nothing, "Table1", Nothing}
            Dim CollectionName As String = "Columns"
            Dim dt As DataTable = Connection.GetSchema(CollectionName, Restrictions)
            For Each TableRow As DataRow In dt.Rows
                Console.WriteLine(TableRow.Item("COLUMN_NAME").ToString)
            Next
    
            DataGridView1.DataSource = dt
            Connection.Close()
    
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by bhd9 Thursday, January 26, 2012 7:13 PM
    Thursday, January 26, 2012 6:29 PM
  • Thank you all for help!!
    Thursday, January 26, 2012 7:15 PM