Connecting to an Access Database

You can use the classes in ADO.NET to connect to a Microsoft Access Database. We use the OLE DB data provider and the System.Data.OleDb namespace for an Access database. This tutorial shows you the steps in creating an access database and creating an application that connects to it. We will be using Microsoft Access 2007 version of Access.

Open Access 2007, and in the home screen, click the Blank Database Icon. On the right side, you will be prompted to name the new database and indicate the location to where the database will be located. Name your database Members.accdb and you can choose any location you want, but for the sake of simplicity, this tutorial will place the file in the C:\ root directory. Click the Create button and you will be brought to Access’ Datasheet View with a new table. You can start adding columns here, but it is easier to switch to Design View and add the columns/fields there so in the ribbon, click View and then Design View. Access will prompt you to name your table, name it Members. Once you are in the Design view, you can now add each field. The default field you will see is the ID field and the data type is AutoNumber. Data types indicate the type of data that the field will store. You will also notice a key beside it. It means that this field will serve as the primary key. Primary key simply means that values in this field should be unique. The AutoNumber data type means that this field will be automatically filled by access as new records are added. An optional column, Description, simply shows the use of a certain field. You can leave it blank or add a useful description about the purpose of the field.

Now add the following fields to our table.

Field Name Data Type
FirstName Text
LastName Text
Age Number

The Text data type indicates that the field is expected to hold a string or text while the Number data type holds numeric data (integers and floating points).

Once you added the fields, return to Datasheet View by going to View > DataSheet View. If you are prompted to save the table, do so. Add the following values to their respective fields. Note that the ID field will automatically be filled by Access.

FirstName LastName Age
John Smith 21
Mark Mayer 23
Alvin Minsky 27
Vince Roster 22
Ben Marcus 18
Ellise Fisher 25

Now that we have some records in our database, we can now create an Application that connects and gets the data from this database. But first, close Microsoft Access 2007 and be sure to save the data.