SQL troubles in VB.net

[detoxx]

I hope you can help me. I'm working on a program that will open any Access database that might be on the system.

The problem I'm having is that I cant get get the SQL statement to show all the tables within the database.

The code im using is as follows:

Imports System.Data.OleDb

Public Class frmtables

    Dim objconnect As OleDbConnection
    Dim objcommand As OleDbCommand
    Dim objreader As OleDbDataReader
    Dim file As String
    Dim sql As String


    Private Sub frmtables_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        file = Form1.txtpath.Text

        objconnect = New OleDbConnection

        objconnect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                                      "Data Source=" & file & ""

        objcommand = New OleDbCommand

        sql = "SELECT * FROM SYSCAT.TABLES"

        objcommand.CommandText = sql
        objcommand.Connection = objconnect
        objconnect.Open()
        objreader = objcommand.ExecuteReader
        cmbotable.Items.Add(objcommand)
        objconnect.Close()
    End Sub
End Class

Now. After about two days of googling looking for that SQL statement, all it seems to do is look for "SYSCAT.mdb" instead of the database selected from the "Open File Dialog" of the previous form :(

Please Help.

Thank you in advance, Detoxx

Edit: I've also used

"USE '" & file & "' GO SELECT * FROM (sys.Tables) GO"

and

"SELECT * FROM INFORMATION_SCHEMA.TABLES"

[Ketsuekiame]

DbProviderFactory factory =
    DbProviderFactories.GetFactory("System.Data.OleDb");

DataTable userTables = null;

using (DbConnection connection =
            factory.CreateConnection())
{

    connection.ConnectionString = "Provider=Microsoft
        .Jet.OLEDB.4.0;Data Source=" & file;
    
    // We only want user tables, not system tables
    string[] restrictions = new string[4];
    restrictions[3] = "Table";
    
    connection.Open();
    
    // Get list of user tables
    userTables =
        connection.GetSchema("Tables", restrictions);
}

// Add list of table names to listBox
for (int i=0; i < userTables.Rows.Count; i++)
    listBox1.Items.Add(userTables.Rows[i][2].ToString());

[detoxx]

Thank you Ketsuekiame :)

I will try this straight away!

[detoxx]

After converting it from C# to VB.net it worked a charm!!!

Im eternally in your debt :P

Thank you!