none
Get list of tables in ACCESS using VB.Net app RRS feed

  • Question

  • I am using Visual Studio 2005 (VB.NET).  My app. is using an Access db in the background.  Once connected to the DB, I want to display a list of all the tables in a combobox on one of the forms.

    How do I go about doing this? 

    Thanks

    Thursday, November 9, 2006 5:16 AM

Answers

  • Hi Christie,

    You can use the following code segment to display the list of tables in a .mdb file

    Dim userTables As DataTable = Nothing
    Dim connection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
    ' c:\test\test.mdb
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\test\\test.mdb"
    ' We only want user tables, not system tables
    Dim restrictions() As String = New String(4) {}
    restrictions(3) =
    "Table"
    connection.Open()
    ' Get list of user tables
    userTables = connection.GetSchema("Tables", restrictions)
    connection.Close()
    ' Add list of table names to listBox
    Dim i As Integer
    For i = 0 To userTables.Rows.Count - 1 Step i + 1
    System.Console.WriteLine(userTables.Rows(i)(2).ToString())
    Next

    Thursday, November 9, 2006 7:04 AM

All replies

  • Hi Christie,

    You can use the following code segment to display the list of tables in a .mdb file

    Dim userTables As DataTable = Nothing
    Dim connection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
    ' c:\test\test.mdb
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\test\\test.mdb"
    ' We only want user tables, not system tables
    Dim restrictions() As String = New String(4) {}
    restrictions(3) =
    "Table"
    connection.Open()
    ' Get list of user tables
    userTables = connection.GetSchema("Tables", restrictions)
    connection.Close()
    ' Add list of table names to listBox
    Dim i As Integer
    For i = 0 To userTables.Rows.Count - 1 Step i + 1
    System.Console.WriteLine(userTables.Rows(i)(2).ToString())
    Next

    Thursday, November 9, 2006 7:04 AM
  • Nihal, thanks your code seems to be working well, except for the error message below:

    "More restrictions were provided than the requested schema ('Tables') supports."

    If I take the restrictions out, the code works, but all tables are displayed, which I don't want.  Any idea how I would fix it??

     

    Thursday, November 9, 2006 7:40 AM
  • Try to use

    connection.GetSchema("Tables", New String() {Nothing, Nothing, "TABLE"}

    Thursday, November 9, 2006 7:53 AM
  • Nihal, I changed the code to

    Dim restrictions() As String = New String(3) {}

    restrictions(3) = "Table"

    connection.Open()

    ' Get list of user tables

    userTables = connection.GetSchema("Tables", restrictions)

    and are now getting my tables with a few temp tables as well.  Much better.

     

    Thanks

    • Proposed as answer by Jagan Krishnan Thursday, May 10, 2012 11:09 AM
    • Unproposed as answer by Jagan Krishnan Thursday, May 10, 2012 11:13 AM
    Thursday, November 9, 2006 8:10 AM
  • Thanks to all who posted.

    I was looking for just this code. I fooled around with it and I got just the User Tables from the DB.

    wireList is a ComboBox

     

     

    Code Snippet

    Dim userTables As DataTable = Nothing

    Dim i As Integer

    Dim restrictions() As String = New String(3) {}

    restrictions(3) = "Table"

     

    Con.Open()

     

    userTables = Con.GetSchema("Tables", restrictions)

     

    Con.Close()

     

    For i = 0 To userTables.Rows.Count - 1

    wireList.Items.Add(userTables.Rows(i)(2).ToString())

    Next

     

     

     

     

    • Proposed as answer by darkmark07 Monday, August 24, 2009 2:37 PM
    Tuesday, November 25, 2008 3:59 AM
  • Thanks Ken.

    Exactly what I was looking for ;-)
    Monday, August 24, 2009 2:36 PM
  • Hi Nihal your given code working fine for oledb provider,but i am using odbc,it showing  the same error "More restrictions were provided than the requested schema ('Tables') supports." after that i try the about code "connection.GetSchema("Tables", New String() {Nothing, Nothing, "TABLE"}" but it returns the null values only pls help me to solve the problem....
    Friday, May 21, 2010 6:37 AM
  • I confirm it. It works!
    Friday, January 21, 2011 8:11 AM
  • confirm.. it works. thanks a lot anyway. 
    Monday, January 24, 2011 1:59 AM
  • try this :
    New String() {Nothing, Nothing, Nothing, "TABLE"}
    Sunday, May 22, 2011 2:27 AM
  • Intermediate\Advance Programmer.  Working with Access 2007 and VS 2008.     This is great snippet.  I was looking for this, too.

    Wednesday, April 4, 2012 1:58 PM
  • I can get the code to display the tables, but I cannot limit it to only user created (it displays all including the system tables).

    I've added the restrictions to my code as noted above, but the code fails.

    New String() {Nothing, Nothing, Nothing, "TABLE"} doesn't work for me.  I am using Visual Studio 2012 with MSAccess 2007 database.  VB.net application. 

    If there is an update to do this, please reply.  

    Thank you in advance.

    Wednesday, August 14, 2013 8:17 PM
  • Ok, I added a filter to look for "TABLE" in the datatable "dt" (Index 3). 

    Instead of only returning the user "TABLE" names, I get all the table names and filter which ones I add to the lstbox.

    This feels like I created a "work around" solution as I don't understand why it wouldn't work as noted by others.  But it does get me off this hurdle and onto others.  :)

    Thanks.


            lstTableNames.Items.Clear()
    
            Dim dt As DataTable = DataOut_DatabaseFunctions.GetTableNames()
            If dt.Rows.Count = 0 Then
                Return
            End If
    
            Dim i As Integer
            For i = 0 To dt.Rows.Count - 1 Step i + 1
                If dt.Rows(i)(3).ToString() = "TABLE" Then
                    lstTableNames.Items.Add(dt.Rows(i)(2).ToString())
                End If
            Next

    • Edited by Warden123 Wednesday, August 14, 2013 9:16 PM add the code
    Wednesday, August 14, 2013 9:14 PM
  • Thanks. Its really working in my project.
    Wednesday, October 16, 2013 1:42 AM
  • Any idea how I can access the columns and rows?
    Saturday, June 17, 2017 2:32 PM
  • Dim restrictions() As String = New String(3) {}

    VB .Net Help Blog

    VB 6.0 Help Blog

    General

    Tuesday, June 30, 2020 3:44 PM
  • Dim restrictions() As String = New String(3) {}

    VB .Net Help Blog

    VB 6.0 Help Blog

    General

    Tuesday, June 30, 2020 3:44 PM