Retrieve a DataTable using Microsoft Access
Tue, Aug 25 2009 17:20
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.
string query = "Select * from Customer";
string cnnString =
DataTable dt = new DataTable("Customer");
using (var cnn = new OleDbConnection(cnnString))
using (var da = new OleDbDataAdapter())
da.SelectCommand = new OleDbCommand(
// Populate the DataTable
Dim query As String = "Select * from Customer"
Dim cnnString As String = _
Dim dt As DataTable = New DataTable("Customer")
Using cnn As New OleDbConnection(cnnString)
Using da As New OleDbDataAdapter()
da.SelectCommand = New OleDbCommand( _
' Populate the DataTable
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.