How to retrieve Ms access tables names from specific database using SQL in VB.NET

Question

Whenever I want to select tables names from specific Ms Access 2007 database, I use this SQL request:

SELECT name from msysobjects WHERE type=1 and flags=0

My VB.NET source code:

 Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
                     "Data Source=" & Text1.Text
        Connection.Open()
        ' sql = "SELECT MSysObjects.Name AS table_name FROM MSysObjects"
        sql = "SELECT name from msysobjects WHERE Type = 1 And flags = 0"
        Dim commande As OleDbCommand
        commande = New OleDbCommand(sql, Connection)
        Dim dataReader As OleDbDataReader

        Try
            dataReader = commande.ExecuteReader()
            While (dataReader.Read())
                tableNameBox.Text = dataReader.GetString(0)
            End While

        Catch ex As Exception
            MsgBox("Problème de connection " & ex.Message())

        End Try

I always get this exception:

no read permission on 'msysobjects'

My purpose is to show tables names in comboBox Thank you

If you have a new question, please ask it as a new question. Do not change this one after you've received a useful valid answer. Thank you for your cooperation. — HansUp
– HansUp, Commented May 26, 2016 at 2:39

Gord Thompson · Accepted Answer · 2016-05-25 20:55:56Z

3

You don't need to access the system tables directly. You can use the GetSchema method to retrieve that information. For example, to retrieve a list of the "regular" (non-system) tables you can use this:

Using conn As New OleDbConnection(myConnectionString)
    conn.Open()
    Dim dt As DataTable = conn.GetSchema("TABLES", {Nothing, Nothing, Nothing, "TABLE"})
    For Each dr As DataRow In dt.Rows
        Console.WriteLine(dr("TABLE_NAME"))
    Next
End Using

For more information, see

OleDbConnection.GetSchema Method (String, String())

answered May 25, 2016 at 20:55

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rodrigo Over a year ago

Hi Gord Thompson, thank you for the your answer. I Really want to informe you that I have edited my post. Thank you. Best regards

Collectives™ on Stack Overflow

How to retrieve Ms access tables names from specific database using SQL in VB.NET

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related