Connect to an Access database in .NET Framework applications

You can connect to an Access database (either an .mdb file or an .accdb file) by using Visual Studio. After you define the connection, the data appears in the Data Sources window. From there, you can drag tables or views onto your design surface.

Prerequisites

To use these procedures, you need:

• Visual Studio
• A Windows Forms or WPF project
• Either an Access database (.accdb file), or an Access 2000-2003 database (.mdb file). Follow the procedure that corresponds to your file type.

Create a dataset for an .mdb file

Connect to databases created with Access 2000-2003 by using the following procedure.

1. Open a Windows Forms or WPF application project in Visual Studio.

2. On the View menu, select Other Windows > Data Sources.

3. In the Data Sources window, click Add New Data Source.

   The Data Source Configuration Wizard opens.

4. Select Database on the Choose a Data Source Type page, and then select Next.

5. Select Dataset on the Choose a Database Model page, and then select Next.

6. On the Choose your Data Connection page, select New Connection to configure a new data connection.

7. If the data source is not Microsoft Access Database File (OLE DB), select Change to open the Change Data Source dialog box and select Microsoft Access Database File, and then select OK.

8. In the Database file name, specify the path and name of the .mdb file you want to connect to, and then select OK.

   

9. Select Next on the Choose your Data Connection page.

10. Select Next on the Save connection string to the Application Configuration file page.

11. Expand the Tables node on the Choose your Database Objects page.

12. Select whatever tables or views you want in your dataset, and then select Finish.

    The dataset is added to your project, and the tables and views appear in the Data Sources window.

View the generated code

The data tools are configured to generate a lot of code automatically when you perform certain operations in the Form Designer. For example, when you drag and drop a table onto the form, a DataGridView is added and code is created to hook up the data with the control. You can view this code in the *.Designer.cs file. Visual Studio adds a number of private members:

private Database11DataSet database11DataSet;
private System.Windows.Forms.BindingSource ordersBindingSource;
private Database11DataSetTableAdapters.OrdersTableAdapter ordersTableAdapter;
private Database11DataSetTableAdapters.TableAdapterManager tableAdapterManager;
private System.Windows.Forms.BindingNavigator ordersBindingNavigator;
private System.Windows.Forms.ToolStripButton bindingNavigatorAddNewItem;
private System.Windows.Forms.ToolStripLabel bindingNavigatorCountItem;
private System.Windows.Forms.ToolStripButton bindingNavigatorDeleteItem;
private System.Windows.Forms.ToolStripButton bindingNavigatorMoveFirstItem;
private System.Windows.Forms.ToolStripButton bindingNavigatorMovePreviousItem;
private System.Windows.Forms.ToolStripSeparator bindingNavigatorSeparator;
private System.Windows.Forms.ToolStripTextBox bindingNavigatorPositionItem;
private System.Windows.Forms.ToolStripSeparator bindingNavigatorSeparator1;
private System.Windows.Forms.ToolStripButton bindingNavigatorMoveNextItem;
private System.Windows.Forms.ToolStripButton bindingNavigatorMoveLastItem;
private System.Windows.Forms.ToolStripSeparator bindingNavigatorSeparator2;
private System.Windows.Forms.ToolStripButton ordersBindingNavigatorSaveItem;
private System.Windows.Forms.DataGridView ordersDataGridView;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn1;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn2;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn3;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn4;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn5;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn6;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn7;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn8;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn9;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn10;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn11;
private System.Windows.Forms.DataGridViewTextBoxColumn dataGridViewTextBoxColumn12;

If you expand the hidden region, you can see that Visual Studio also adds a large amount of code to set up the DataGridView control with data binding to the table you dragged to the form.

Also, in the main form code-behind file, Visual Studio adds code that processes the save action to save interactive changes to the data, and the code that loads the table into the table adapter.

private void ordersBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
    this.Validate();
    this.ordersBindingSource.EndEdit();
    this.tableAdapterManager.UpdateAll(this.database11DataSet);

}

private void Form1_Load(object sender, EventArgs e)
{
   // TODO: This line of code loads data into the 'database11DataSet.Orders' table. You can move, or remove it, as needed.
   this.ordersTableAdapter.Fill(this.database11DataSet.Orders);
}

Congratulations! With a little help from Visual Studio, you've created a form-based editing experience for an Access data table.

Next steps

The dataset that you just created is available in the Data Sources window. You can now perform any of the following tasks:

• Select items in the Data Sources window and drag them onto your form or design surface (see Bind Windows Forms controls to data in Visual Studio or WPF data binding overview).

• Open the data source in the Dataset Designer to add or edit the objects that make up the dataset.

• Add validation logic to the ColumnChanging or RowChanging event of the data tables in the dataset (see Validate data in datasets).

Related content

• Add connections
• WPF data binding overview
• Windows Forms data binding