Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

August 25, 2009

Retrieve a DataTable using Microsoft Access

Filed under: C#,Data,VB.NET @ 7:20 pm

Despite the fact that there is a free version of SQL Server called SQL Server Express, there are still applications that require using a Microsoft Access database. But since these are dwindling in number, there are few articles or posts devoted to accessing Access.

This post (and my next post that demonstrates how to update Access data)attempt to rectify that issue.

The code is first shown in both VB and C#. It is then described in detail below.

NOTE: Be sure to set a reference to System.Data.OleDb.

In C#:

string query = "Select * from Customer";
string cnnString =
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;";
DataTable dt  = new DataTable("Customer");

using (var cnn = new OleDbConnection(cnnString))
{
    cnn.Open();

    using (var da = new OleDbDataAdapter())
    { 
        da.SelectCommand = new OleDbCommand(
            query, cnn);

        // Populate the DataTable
        da.Fill(dt);
    }
}

In VB:

Dim query As String = "Select * from Customer"
Dim cnnString As String = _
   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;"
Dim dt As DataTable = New DataTable("Customer")

Using cnn As New OleDbConnection(cnnString)
    cnn.Open()

    Using da As New OleDbDataAdapter()

        da.SelectCommand = New OleDbCommand( _
            query, cnn)

        ‘ Populate the DataTable
        da.Fill(dt)
    End Using
End Using

The code begins by declaring several variables. The first variable is the Select statement. Change it to select the desired fields from the desired table.

NOTE: Even though Select * was shown in this example, good programming practices dictates specifying field names in the Select clause and not using the Select * syntax.

The connection string uses the Jet OleDb data provider. Change the connection string Data Source property to the directory and name of your Access database file. If you provide no directory (like in this example), it will assume that the file is located in the same directory as the executing application.

The code then creates an instance of the DataTable, giving it a name of Customer. Rename the table as appropriate for your application.

The first using statement defines the connection. The connection is then opened. The connection is automatically closed at the end of the using block.

The second using statement defines a data adapter. The SelectCommand property of the data adapter is set to the defined query and opened connection. The data adapter is then used to fill the DataTable.

Use the technique defined here to display the resulting DataTable.

Enjoy!

1 Comment

  1.   George Levrier — October 5, 2010 @ 4:07 pm    Reply

    Thanks, this was exactly what I was looking for. Why can’t other people keep is as simple as you did.

RSS feed for comments on this post. TrackBack URI

Leave a comment

*

© 2014 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs