Read Access File into a DataSet

Question

Is there an easy way to read an entire Access file (.mdb) into a DataSet in .NET (specifically C# or VB)?

Or at least to get a list of tables from an access file so that I can loop through it and add them one at a time into a DataSet?

Answer 1

You should be able to access it using an OleDbConnection.

Heres a tut on DB access using it for MS Access files.

In terms of getting the table names, back in my VB6 days I always used ADOX, not sure how they do this in .NET now.. Although I know there is a system table in the access file - wanna say "mso...". I google!

EDIT

Ah ha! msysobjects !! xD

Answer 2

MSDN has an article on how to use ADO.NET to connect and edit records in an Access database. Once your OleDB connection is made, you can easily create your DataReader/DataAdapter and process as needed.

EDIT: Gah! Curse you Rob and your god-like typing abilities!!! 8^D

Answer 3

You can get a list of tables by querying the schema.

Answer 4

Thanks for the suggestions. I was able to use those samples to put together this code, which seems to achieve what I'm looking for.

Using cn = New OleDbConnection(connectionstring)
    cn.Open()
    Dim ds As DataSet = new DataSet()

    Dim Schema As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
    For i As Integer = 0 To Schema.Rows.Count - 1
     Dim dt As DataTable = New DataTable(Schema.Rows(i)!TABLE_NAME.ToString())

     Using adapter = New OleDbDataAdapter("SELECT * FROM " + Schema.Rows(i)!TABLE_NAME.ToString(), cn)
      adapter.Fill(dt)
     End Using

     ds.Tables.Add(dt)
    Next i
End Using

Answer 5

There is a discussion on this point in Less Than Dot. Here is one example of code from the discussion.

   public DataTable GetColumns(string tableName)
    {
        string[] restrictions = new string[4];
        restrictions[2] = tableName;

        _connDb.Open();

        DataTable mDT = _connDb.GetSchema("Columns", restrictions);

        _connDb.Close();

        return mDT;
    }

Answer 6

Your original question as worded is nonsense:

Is there an easy way to read an entire Access file (.mdb) into...

You certainly don't want the entire contents of the MDB file. What you want is the contents of your data tables that are stored in the MDB. Keep in mind that you don't want the contents of the system tables, either.

The key point:

You're asking about JET, not about ACCESS.

Jet is the database engine that ships as the default data store of Access (and in which Access's own objects are stored). But "Access" means something much more than just the data tables.

Whenever you ask a question and confuse Access and Jet, you will likely get at least some unuseful answers.

And you'll get scolded by the likes of me, because developers really should know better than to obfuscate crucial distinctions.