How do I extract table names from MS Access database using ADO

Question

i tried using this code :

OleDbConnection c = new OleDbConnection(con);
string SQLS = "SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Name Not Like 'MsyS*' AND MSysObjects.Type=1 ORDER BY MSysObjects.Name";
OleDbDataAdapter da = new OleDbDataAdapter(SQLS, c);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;

But i got this exception:

Record(s) cannot be read; no read permission on 'MSysObjects'.

Now, i need to transfer the entire ms-access database to mysql programmaticaly, thus i need the database names. How do I work my way around this error?

Martin Buberl · Accepted Answer · 2011-02-11 20:11:29Z

6

using System;
using System.Data;
using System.Data.OleDb;

public class DatabaseInfo {    
    public static void Main () { 
        String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
        OleDbConnection con = new OleDbConnection(connect);
        con.Open();  
        Console.WriteLine("Made the connection to the database");

        Console.WriteLine("Information for each table contains:");
        DataTable tables = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});

        Console.WriteLine("The tables are:");
            foreach(DataRow row in tables.Rows) 
                Console.Write("  {0}", row[2]);


        con.Close();
    }
}

///taken from http://www.java2s.com/Code/CSharp/Database-ADO.net/Getalltablenames.htm

edited Feb 11, 2011 at 20:11

Martin Buberl

47.4k26 gold badges103 silver badges146 bronze badges

answered Dec 17, 2010 at 9:33

aristotaly

3992 silver badges15 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 11:54:19Z

1

You can access it like this:

OleDbConnection conn =
 new OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
    "C:\\phycoaide\\phycoaide.mdb;Persist Security Info=False;");

// retrieving schema for a single table
OleDbCommand cmd = new OleDbCommand("taxa", conn);
cmd.CommandType = CommandType.TableDirect;
conn.Open();
OleDbDataReader reader =
 cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable schemaTable = reader.GetSchemaTable();
reader.Close();
conn.Close();

See http://harborsparrow.blogspot.com/2009/05/c-code-to-get-schema-of-access-table.html for more details.

EDIT:
Ok, so then you can retrieve all the tables using a solution like this: How do I list all the queries in a MS Access file using OleDB in C#? and then loop through them.

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Dec 14, 2010 at 19:54

MAW74656

3,57921 gold badges80 silver badges120 bronze badges

4 Comments

aristotaly Over a year ago

i going with this assuming i do not know what the table of the database are .

MAW74656 Over a year ago

Glad I could help. Please mark this as an answer if it does the trick! :-)

aristotaly Over a year ago

but i seek different kind of answer...:)

aristotaly Over a year ago

i assume that i do not know the name of the tables in this database

Mino · Accepted Answer · 2018-04-18 19:11:18Z

0

You can try Kros.Utils.MsAccess. This package has support for MsAccess database schema.

answered Apr 18, 2018 at 19:11

Mino

3351 gold badge2 silver badges12 bronze badges

Collectives™ on Stack Overflow

How do I extract table names from MS Access database using ADO

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related