Click here to Skip to main content
13,139,569 members (52,063 online)
Click here to Skip to main content
Add your own
alternative version

Stats

33.4K views
560 downloads
45 bookmarked
Posted 31 Aug 2017

Using Microsoft Access Database In C# . . . ADO.NET In Winforms/WPF For Beginners

, 31 Aug 2017
Rate this:
Please Sign up or sign in to vote.
Read & Write Data To Microsoft Access (.mdb, .accdb) . . . SQL Queries Basics Through The Example Of Access . . . Winforms | WPF . . .

Downloads

There are no downloads available. It means you should read the article, create a C# Project and write the code by yourself. However, if you want, you can download a finished project for Visual Studio 2008 by clicking here, but note that this version of Visual Studio is obsolete, now I going to migrate to Visual Studio 2015 and Roslyn C# compiler.

History

17.12.2015 Tip/Trick is post for the first time. This is just the beginning. Keep for updates.

16.07.2016 Going to migrate from Visual Studio 2008 to 2015.

Contents

Getting Started With Microsoft Access Of Traditional Format (.mdb)

In this chapter we will learn ADO.NET basics, SQL syntax basics, and basic SQL data types (counter, text and number).

Goal

Create a basic Access database (of old *.mdb format) with 1 table "Customers", with 3 fields of basic data types:

  • "id" of Counter type (we will learn why this field is need in the table)
  • "FullName" of Text type (with Customer's Full Name)
  • "PassportId" of Number type (with Customer's Passport's Series & Number)

Create a Winforms application to provide a flexible, managed, quick, free access to this database for clerks of our store.

Winforms application will allow clerks to:

  • View the list of customers as a table.
  • Modify this table and save changes to database.
  • Don't have problems if any incorrent input or other frequency mistakes (i.e. opening file already using by other proccess) - application will be stabilized via try-catches, etc. ("foolproof") and will not crash or display peesky & unclear error dialogs.

What Do We Need

For Develop

1. .NET Framework of any version.

2. Visual Studio of any version (starting from 2002). I using 2008, 2010, 2013.

3. MDAC and Microsoft.Jet.4.0 (both included to Windows at least of XP version).

4. MS Access (need only for create & design DB, not need to get our application to work, it uses MDAC directly).

For Redistribute Our Application

1. .NET Framework of any version.

2. MDAC and Microsoft.Jet.4.0 (both included to Windows at least of XP version).

Preparing Your Application Project & DB

1. Create a new Winforms Application Project.

2. Open Microsoft Access. Create a database in project's bin\Debug path, name it Store.mdb

3. Create a table in Design View (Builder).

Add two (so far, only two) fields:

  • FullName of Text type
  • PassportId of Number type

Don't set any key fields.

Save the Table with name of Customers

4. Fill the table via a few records. It is not necessary to open, view and edit table via ADO, but these records will be need for our experiments.

5. Don't forget to close the table in Access. It shouldn't be opened in Access when runinng ADO application which uses it. Some time later we will know why it need.

Creating An Application

1. Go To Form1.cs Code.

Before namespace block, add:

using System.Data.OleDb;

Recommended Result (let's separate our custom usings from default, by empty line, comment line, etc.):

2. In Form's Load or Shown event, let's try connect to the DB. This procedure is similar to the opening of a simply text/binary file to read & write (initializing a file stream):

var conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=Store.mdb;");
conn.Open();

Note: usually conn.Open() isn't required to call, because if DB isn't opened, it will automatically open when we start manipulate of tables via most of kinds. But it allows we test the connection, because Open() will throw an exception if something goes wrong. Try-catch around the Open() is one of the attributes of stabile application. But so later.

3. Let's try our code to build & debug, and test the connection.

Everything is working? Form is shown? No exceptions?

Congratulations! You lucky! You are happy owner of Windows of outmoded 32-bit architecture, probably you also have a retro PC or laptop, of 3 years or older, and your Windows is 7, Vista, XP, or even older.

Because, if we will run it on 64-bit Windows (which are now almost replaced the 32-bit fellows), it will SURELY throw an exception:

And this is a very common problem. Let's look at Google's queries history:

But what does all this mean? Is Jet 4.0 provider (which needed for *.mdb) really not pre-installed in Windows x64?

No, it is pre-installed, but only in 32-bit version. C# applications by default are have "Any CPU" architecture: on Windows x86 they running as 32-bit, and on Windows x86 they running as 64-bit.

And when 64-bit application tries to use the 32-bit library, the trouble occurs.

What can we do?

Problem: Provider Is Not Registered. How to change our C# application's platform (architecture) from Any CPU to x86?

Enable Advanced Build Configuration (For Visual Studio 2008)

 

 

 

 

 

Change Build Platform To x86

 

 

 

Warning: now directory with output EXE is changed from /bin/Debug/ to /bin/x86/Debug/.

We should replace our database file to new directory. Also I recommend delete the old "Debug" folder (because it will confuse any person if he doesn't know about plaftform changed), and together delete the empty "Release" folder.

After that, we can build & debug the application - and it will work.

Now, let's return to our main goal.

At first, we should provide a view of Customers table in the form.

1. To view table as the table on the form, we should create a DataGridView control on the form. We can do it via Designer:

2. But DataGridView isn't the entity of data which we can use directly.

Figuratively speaking, grid is just tеlevision receiver - which should be connected to TV channel to view it.

After we connected the DB, we should select a data from the Customers table, put it to data set (the entity of the data in the RAM, like the matrix - 2-dimensional array of table's cells), and next bind grid to data set as to data source.

At first, in Form_Load, after we connected DB, we should create an instance of the DataSet or DataTable class. That's different? DataTable is data set for the single table, and DataSet (roughly speaking) is the collection of different DataTables.

In our case, we using only 1 table, DataTable is more better:

var dtCustomers = new DataTable();

3. Next, let's return to Form1_Load event handler.

After we connected to DB, we should run this SQL query:

SELECT * FROM [Customers];

Where asterisk (*) means "all collumns' content", and "[Customers]" is the table's name. 

Note: square brackets are need for case if table's name contains spaces. But it's always not recommend to create these spaces. If our table name name not contains spaces, we can remove the square brackets.

To run it, it's best to use OleDbDataAdapter class, on Fill() method it executes the query and fills the data table:

var adapter = new OleDbDataAdapter("SELECT * FROM Customers",
                conn);
adapter.Fill(dtCustomers);

Next, we can just bind the dtCustomers to grid:

dataGridView1.DataSource = dtCustomers;

Result Form_Load code:

var conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=Store.mdb;");
conn.Open();

var dtCustomers = new DataTable();

var adapter = new OleDbDataAdapter("SELECT * FROM Customers;",
    conn);
adapter.Fill(dtCustomers);

dataGridView1.DataSource = dtCustomers;

Let's run it to debug. Bingo! Now user can view the Customers table!

But user can't to edit the table in anyway. More precisely, he can add, edit, delete row in grid, but these changes willn't be wrote to DB.

How to correct it? Read next.

How To Allow User Edit The Table

Recently we learned basics of the SELECT SQL query syntax, which is using to get the data from table.

Now we will learn other SQL queries - INSERT, UPDATE and DELETE. These queries are using to add/insert, edit and delete table's rows.

Add/Insert Row SQL Query

Let's create a button, and on Click write our code.

We should run such query to insert a row:

INSERT INTO [Customers] (FullName, PassportId) VALUES ('Test Name', 123456);

Where Customers is table name, in first brackets are list of columns to insert values, and in second brackets are list of values to these columns.

Single quotes on "FullName" column's value are mean that this value is string.

To execute simply SQL query with or without returned result, we should use OleDbCommand class. We need transfer OleDbConnection object to it, therefore let's re-declare our connection in local area, to make it accessible from button's Click event handler:

public partial class Form1 : Form
{
    OleDbConnection conn;
    
    public Form1()
    {
        InitializeComponent();
    }
    
    private void Form1_Load(object sender, EventArgs e)
    {
        conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=Store.mdb;");
        conn.Open();
        ...
    }
    
    private void btnAddRow_Click(object sender, EventArgs e)
    {
    }

    ...

Next, let's return to btnAddRow_Click event handler, and write this code:

using (var cmd = new OleDbCommand("INSERT INTO [Customers] (FullName, PassportId) VALUES ('Test Name', 123456);", conn))
{
    cmd.ExecuteNonQuery();
}

Run the debug. It will add a row on button clicked. To see it in DataGridView, we should restart the application, to re-connect to DB and re-fill the DataTable.

If we want to instantly refresh a grid, we can re-declare adapter & data table in local area, and next (after insert) just clear & re-fill DataTable:

dtCustomers.Clear();
adapter.Fill(dtCustomers);
Update Row Query

We can just execute such query:

UPDATE [Customers] SET FullName='Updated Row', PassportId=9876;

But it will update only first table's row. How to upgrade the row that we want?

...And now we gradually moved to, why 'id' (AutoCounter Column) is useful.

Let's open our table in Access and add a field named "id" of AutoCounter type.

It automatically will set incrementally for all existing rows:

And when we'll add a row, it also will be automatically set to new, unique value.

Let's now close the Access and return to our application.

Because table now contains column with unique values per rows, we can use this column to identify the row to update, via WHERE keyword, i.e.:

UPDATE [Customers] SET FullName='Updated Row', PassportId=9876 WHERE id=3;

This query will update row with "id" value of 3.

Let's write the C# code to update a row selected in grid:

using (var cmd = new OleDbCommand("UPDATE [Customers] SET FullName='Updated Row', PassportId=9876 WHERE id=" + dataGridView1.CurrentRow.Cells["id"].Value.ToString(), conn))
{
    cmd.ExecuteNonQuery();
}

dtCustomers.Clear();
adapter.Fill(dtCustomers);
Deleting Row Query

For DELETE query, we also need AutoCounter - to identify the row:

DELETE FROM [Customers] WHERE id=1;

Well, now we know how to insert, update or delete row via SQL. But what we will benefit from it? 
For convenient use of grid, we must implement the following logic:
- when user adds a new row to grid, it should add to the table via INSERT query;
- when user edits a cell of the grid, it should be edit in the table using UPDATE query;
- when users selects a row (by click row's left header) and press Delete, it should be remove from table using DELETE query. Also it should work for muliply rows select.

And how to do it? 
At first glance, it is very difficult and requires a complex logic based on a few of DataGridView events i.e  RowsAdded, RowsDeleted, CellValueChanged, and/or some etc.
But avoid useless reinventing the wheel! We should always check first if there already is finished implementation of our idea, and implement it by ourself only if there are no implementation, or no implementation suitable for our need.

Encapsulating Insert & Update & Delete Queries Into OleDbDataAdapter

It turns out that there already is a good implementation of our idea.

We can just initialize the OleDbCommands on form load, set whem to OleDbDataAdapter - and next it will automatically call commands with necessary parameters on adapter.Update(dtCustomers) call.

To create commands, after adapter.Fill() call, add these lines:

var cmdInsert = new OleDbCommand("INSERT INTO Customers (FullName, PassportId) VALUES (?, ?);", conn);
cmdInsert.Parameters.Add("p1", OleDbType.BSTR, -1, "FullName");
cmdInsert.Parameters.Add("p2", OleDbType.Integer, 5, "PassportId");
adapter.InsertCommand = cmdInsert;

var cmdUpdate = new OleDbCommand("UPDATE Customers SET FullName = ?, PassportId = ? WHERE id = ?;", conn);
cmdUpdate.Parameters.Add("p1", OleDbType.BSTR, -1, "FullName");
cmdUpdate.Parameters.Add("p2", OleDbType.Integer, 5, "PassportId");
cmdUpdate.Parameters.Add("p3", OleDbType.Integer, 5, "id");
adapter.UpdateCommand = cmdUpdate;

var cmdDelete = new OleDbCommand("DELETE FROM Customers WHERE id = ?;", conn);
cmdDelete.Parameters.Add("p1", OleDbType.Integer, 5, "id");
adapter.DeleteCommand = cmdDelete;

Next, we have to save a grid to DB, after user modified it.

We can create a "Save" button (user will manually click it, in it will be save data), or just save it automatically in Form_Close. Anycase, we should use this code after dataset is modified:

adapter.Update(dtCustomers);
Using OleDbCommandBuilder To Automatically Generate Insert & Update & Delete Queries Encapsulated Into OleDbDataAdapter

ADO.NET can automatically generate INSERT, UPDATE and DELETE queries for OleDbDataAdapter.

We can do it by just one line of code:

new OleDbCommandBuilder(adapter);

Just put this line after adapter.Fill() call, instead of manually commands creating.

How To Work With Grid Programmatically

Simplest way to programmatically work with table is working with DataGridView.

Note: DataGridView isn't stores the loaded table in RAM, it is just television receiver ("client"), which binded to DataTable/DataSet, and really all data stores in DataTable/DataSet. It means what using DataGridView is redundantly if we just want to work with table programmatically and not need to use grid by its general purpose (represent the data in UI in table-like style). But if we always using grid to display and/or edit data, we can also use it to programmatically access the data, it is quite normal solution in this case.

Warning: we just can't add or insert a row to binded DataGridView programmatically, for this we should use DataTable or SQL INSERT query; it's very strange, and it is it. But we can delete or modify a row in DataGridView.

How does it looks in general?

I think you have noticed what database's table is very similar to 2-dimensional array (matrix). Table has several rows, and each row has several cells. Not surprisingly what working with a table is very similar to working with a matrix.

Get & Set Cell's Value

To get or set value to DataGridView's cell, we should at first get the needed row, next get the needed cell, and at last get/set the cell's value.

Also in the case of matrix, grid's row can be got by index from 0 to count - 1, and cell can be got by column's index from 0 to count - 1;

Warning: and also in the case of matrix, all indexes should reference really existing row & cell. Otherwise, it will throw and exception.

Let's get the value of second cell of second row, convert it to string and show it in MessageBox, and next try to set this value from string:

var row = dataGridView1.Rows[0];
var cell = row.Cells[1];
MessageBox.Show(cell.Value.ToString());
dataGridView1.BeginEdit(false); // begins edit mode (i.e. if we manually double-clicked cell)
cell.Value = "123";
dataGridView1.EndEdit(); // ends edit mode, saves to DT
adapter.Update(dtCustomers); // writes DT changes to DB (adapter should be set up properly - read above)

More shorter:

MessageBox.Show(dataGridView1.Rows[0].Cells[1].Value.ToString());
dataGridView1.BeginEdit(false);
dataGridView1.Rows[0].Cells[1].Value = "123";
dataGridView1.EndEdit();
adapter.Update(dtCustomers);

Shortest:

MessageBox.Show(dataGridView1[1, 0].Value.ToString());
dataGridView1.BeginEdit(false);
dataGridView1[1, 0].Value = "123";
dataGridView1.EndEdit();
adapter.Update(dtCustomers);

As we can test, all it properly works for both Text-typed and Number-typed columns.

Get Row Count & Cell Count

At first sight, we can just use this code:

var rowCount = dataGridView2.RowCount;
MessageBox.Show(rowCount.ToString());

Or:

var rowCount = dataGridView2.Rows.Count;
MessageBox.Show(rowCount.ToString());

But in fact, if grid has AllowUserToAddRows = true (and it's by default), we will got count of all rows including last empty row:

To get really last row in this case, we should ignore this row:

var rowCount = dataGridView2.RowCount - 1;
MessageBox.Show(rowCount.ToString());

To get cells count, we not need to get a row and get its cells count, we can just get columns count:

var cellCount = dataGridView2.ColumnCount;
MessageBox.Show(cellCount.ToString());
Remove Row

Remove row by index:

dataGridView2.Rows.RemoveAt(0); // first row

Remove row by an reference:

var row = ...
dataGridView2.Rows.Remove(row);
Add Row

As mentioned above, it is impossible to add a row to grid if it binded to data source. dataGridView.Rows.Add() will just throw the exception.

We should do it directly via DataTable or via SQL.

It is also impossible.

Some Other Data Types And Their Representation In Grid

Let's improve our "Customers" table, by adding some different columns for some different purposes.

Memo, Date/Time, Currency

Memo is same as Text type, but it doesn't have a limitation to length (symbols count). It not causes the exception on INSERT/UPDATE if value is very long.

Let's add a column named "Notes" by type of Memo. This column will contain special notes about each customer.

As we can see, represents in grid correctly and almost same as Text type.

Date/Time is more complex type. It is Text type that contains a date and time, and in Access it can represent as different string formats.

Let's add a column named "DOB" by type of Date/Time. This column will contain customer's date of birth.

As we can see, in Access it can represent in one of different formats what we can select:

In DataGridView, it represents without considering of format. Date already represents as default system short date, and time represents as hh:mm:ss format, i.e.:

Also, there is unconvenient that DataGridView (same as Access grid) don't have a DataTimePicker column which allow select a data from dropdown or enter manually, but all this doesn't matter by and large.

Currency is complex type based on Number.

Let's add a column named "TotalPurchasesAmount" by type of Currency. This column will contain customer's total purcases amount (which can be used to do discounts for most profitable customers).

As we can see, it represents in DataGridView as raw Number value:

Yes/No

This is a special type for storing boolean values (logical type).

Let's add a column named "HasDiscount" by type of Yes/No. This column will indicate that customers have a discount.

As we can see, it represents in DataGridView also properly as in Access:

Hyperlink

This type is almost same as Text, which surround the value into "#".

Let's add a column named "HomePageURL" by type of Hyperlink. This column will optionally contain customer's website URL.

As we can see, it doesn't represents in DataGridView properly:

This is wrong. In Access grid it represents as clickable blue underlined link:

Image (Via OLE Object). Troubles With Cell's Size

Let's add a field for photo of the customer.

1. Open the database in Access. Open the table in the Design View (Builder).

2. Add a field, named "Photo", of "OLE Object" type.

3. Save the table.

Next, let's fill the new field by values. This operation for image fields is a bit different for some one for ordinary string or integer fields.

1. Open the table to edit.

2. To fill a cell by image, right-click it, and select "Insert Object..." menu item.

3. To create an image from a file, check "Create From File" radio button. Next, click the "Browse" button, and select your image file. Finally, click "OK", and image will be copied into your DB and inserted to the cell.

3.1. Also, we can draw our image in Paint just now, and insert directly to cell. In this case, check "Create New", select the "Bitmap Image" object type, click OK. Now we can draw the picture in the Paint, and close it after we'll end. We will get the same result as if we will create it from file.

4. Close our table and our DB in Access.

Next, let's run our C# application. We will see something like this:

We seeing the images, but they are shrinked to default row height & column width. How to fix it?

Simplest way is to set AutoSizeColumnsMode and AutoSizeRowsMode to AllCells value. Now all cells are automatically fitting to content. But it automatically sets ColumnHeadersHeightSizeMode to AutoSize from EnableResizing, and equal for RowHeadersHeightSizeMode, i.e. now user can't to resize columns and rows.

More precisely way is set AutoSizeColumnsMode back to None, and set auto sizing only for image column, i.e.:

dataGridView1.Columns["Photo"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; // "Photo" is column's name

We can put this code after grid's DataSource setted (because when it not setted, where are no columns in grid, and this line will throw NullArgumentException).

Now we can resize all columns, except Photo, but already can't resize any row.

Most elegancy way is implement this algorithm:

1. Programmatically enable both autosizes.

2. Remember automatically setted sizes (width of Photo column, and heights for rows).

3. Programmatically disable both autosizes.

4. Set sizes to minded values.

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

var photoColumnWidth = dataGridView1.Columns["Photo"].Width;

var rowHeights = new List<int>();
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    rowHeights.Add(dataGridView1.Rows[i].Height);
}

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;

dataGridView1.Columns["Photo"].Width = photoColumnWidth;

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    dataGridView1.Rows[i].Height = rowHeights[i];
}

Now everything with Image is perfect.

Binding Data To Another Controls Than Grid

Bind TextBox's Text to String-typed field:

textBox1.DataBindings.Add("Text", dtCustomers, "FullName");

"Text" is name of TextBox's property which we want bind to field.

Someway we can bind it to field of any other type (it will be converted by ToString()):

textBox1.DataBindings.Add("Text", dtCustomers, "PasswordId");

"Photo" field also can be bind to textBox, but we will see "Byte[] Array" text and willn't see any interested, we should use PictureBox to see the Image:

pictureBox1.DataBindings.Add("Image", dtCustomers, "Photo");

If we want remove a binding from control, we can just use:

textBox1.DataBindings.Clear();

Or (if we using many different bindings for one control and want remove only one of them):

textBox1.DataBindings.Remove(textBox2.DataBindings["Text"]);

Result: Full Code

Important: Any CPU should be changed to x86.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
//-------------------------
using System.Data.OleDb;

namespace Getting_Started_Winforms_To_MDB__VS_2008__
{
    public partial class Form1 : Form
    {
        OleDbConnection conn;
        OleDbDataAdapter adapter;
        DataTable dtCustomers;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=Store.mdb;");
            conn.Open();

            dtCustomers = new DataTable();

            adapter = new OleDbDataAdapter("SELECT * FROM Customers;",
                conn);
            adapter.Fill(dtCustomers);

            /*var cmdInsert = new OleDbCommand("INSERT INTO Customers (FullName, PassportId) VALUES (?, ?);", conn);
            cmdInsert.Parameters.Add("p1", OleDbType.BSTR, -1, "FullName");
            cmdInsert.Parameters.Add("p2", OleDbType.Integer, 5, "PassportId");
            adapter.InsertCommand = cmdInsert;

            var cmdUpdate = new OleDbCommand("UPDATE Customers SET FullName = ?, PassportId = ? WHERE id = ?;", conn);
            cmdUpdate.Parameters.Add("p1", OleDbType.BSTR, -1, "FullName");
            cmdUpdate.Parameters.Add("p2", OleDbType.Integer, 5, "PassportId");
            cmdUpdate.Parameters.Add("p3", OleDbType.Integer, 5, "id");
            adapter.UpdateCommand = cmdUpdate;

            var cmdDelete = new OleDbCommand("DELETE FROM Customers WHERE id = ?;", conn);
            cmdDelete.Parameters.Add("p1", OleDbType.Integer, 5, "id");
            adapter.DeleteCommand = cmdDelete;
            */

            new OleDbCommandBuilder(adapter);

            dataGridView1.DataSource = dtCustomers;
           
            try
            {
                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

                var photoColumnWidth = dataGridView1.Columns["Photo"].Width;

                var rowHeights = new List<int>();
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    rowHeights.Add(dataGridView1.Rows[i].Height);
                }

                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
                dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;

                dataGridView1.Columns["Photo"].Width = photoColumnWidth;

                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    dataGridView1.Rows[i].Height = rowHeights[i];
                }
            }
            catch { }

            textBox1.DataBindings.Add("Text", dtCustomers, "FullName");
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            adapter.Update(dtCustomers);
        }
    }
}

Migrating To New 2007+ Format (.accdb)

Mostly, new ACCDB format has no differs from old MDB format in terms of development. It also uses OleDb (as ADO.NET's component), have similar SQL syntax, supports old data types from MDB.

But still he has some differences, and the first difference is different provider.

What Do We Need

Windows-default Microsoft Jet 4.0 Provider is written quite a long time ago, and since then has not changed in earnest. Sure, it doesn't support new ACCDB format.

ACCDB format requires a new provider, Microsoft.ACE.OLEDB.12.0 or better, which is included to Microsoft Access 2007+.

For development, we anyway should install Access - which contains the provider.

For redistibutable, we should choice, download and install one of redistributable (runtime) packages from Microsoft.

Which redistributable (runtime) of which version and which architecture contains which provider(s). Based on my experience).
Runtime's VersionRuntime's ArchitectureContains Provider(s);
2007 Office System Driver: Data Connectivity Componentsx86 (no x64)Microsoft.Ace.OleDb.12.0
Microsoft Access Database Engine 2010 Redistributablex86Microsoft.Ace.OleDb.12.0
Microsoft Access Database Engine 2010 Redistributablex64(not tested yet, at least 12.0 probably)
Microsoft Access Runtime 2013x86Microsoft.Ace.OleDb.12.0, Microsoft.Ace.OleDb.15.0
Microsoft Access Runtime 2013x64(not tested yet, at least 12.0 and 15.0 probably)

As we can see, most compatible provider is Ace 12.0.

Result Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
//-------------------------
using System.Data.OleDb;

namespace Getting_Started_Winforms_To_ACCDB__VS_2008__
{
    public partial class Form1 : Form
    {
        OleDbConnection conn;
        OleDbDataAdapter adapter;
        DataTable dtCustomers;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            conn = new OleDbConnection("Provider=Microsoft.Ace.OleDb.12.0;Data Source=Store.accdb;");
            conn.Open();

            dtCustomers = new DataTable();

            adapter = new OleDbDataAdapter("SELECT * FROM Customers;",
                conn);
            adapter.Fill(dtCustomers);

            /*var cmdInsert = new OleDbCommand("INSERT INTO Customers (FullName, PassportId) VALUES (?, ?);", conn);
            cmdInsert.Parameters.Add("p1", OleDbType.BSTR, -1, "FullName");
            cmdInsert.Parameters.Add("p2", OleDbType.Integer, 5, "PassportId");
            adapter.InsertCommand = cmdInsert;

            var cmdUpdate = new OleDbCommand("UPDATE Customers SET FullName = ?, PassportId = ? WHERE id = ?;", conn);
            cmdUpdate.Parameters.Add("p1", OleDbType.BSTR, -1, "FullName");
            cmdUpdate.Parameters.Add("p2", OleDbType.Integer, 5, "PassportId");
            cmdUpdate.Parameters.Add("p3", OleDbType.Integer, 5, "id");
            adapter.UpdateCommand = cmdUpdate;

            var cmdDelete = new OleDbCommand("DELETE FROM Customers WHERE id = ?;", conn);
            cmdDelete.Parameters.Add("p1", OleDbType.Integer, 5, "id");
            adapter.DeleteCommand = cmdDelete;
            */

            new OleDbCommandBuilder(adapter);

            dataGridView1.DataSource = dtCustomers;
           
            try
            {
                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

                var photoColumnWidth = dataGridView1.Columns["Photo"].Width;

                var rowHeights = new List<int>();
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    rowHeights.Add(dataGridView1.Rows[i].Height);
                }

                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
                dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;

                dataGridView1.Columns["Photo"].Width = photoColumnWidth;

                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    dataGridView1.Rows[i].Height = rowHeights[i];
                }
            }
            catch { }

            textBox1.DataBindings.Add("Text", dtCustomers, "FullName");
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            adapter.Update(dtCustomers);
        }
    }
}

Migrating To WPF

Project Platform Architecture: x86 Or Any CPU?

WPF has fundamentally own UI controls, own binding system, but doesn't provides any own tools for DBs.
Therefore we still will use Jet / Ace provider, via OleDB, via ADO, via ADO.NET.
We still should build our project in x86 for Jet (.mdb), and in x86/Any CPU for Ace (.accdb) (in dependent on which architecture of Microsoft Access Database Engine has installed).

Connecting DataTable/DataSet To DB

There also no sensible differences. Only one difference is that System.Data namespace not imported by default, and we should do it manually, same as System.Data.OleDb:

using System.Data;
using System.Data.OleDb;

Grid In WPF

And here serious differences begin. That's because WPF is fundamentally different GUI framework, probably written by separate programmers team, who did not try to provide any compatibility with Winforms.

At first, there are no DataGrid or DataGridView by default in .NET 3.0 and .NET 3.5!

Where Is WPF Grid In .NET 3.0, 3.5 (If We Developing In Visual Studio 2008)?

Obviously, if there are no default tool, we should find and download a third-party tool.

In our case, it is WPF Toolkit library, which contains DataGrid and other useful WPF controls for .NET 3.0/3.5.

1. Go to http://wpf.codeplex.com/ and download the last WPF Tookit installer (direct link).

2. It is a regular MSI installer, therefore I do not think you will have any problems with tookit's installation.

3. Restart the Visual Studio. Scroll the "Toolbox" to down. We should see a "WPF Toolkit" tab with a few controls, including DataGrid.

Just put this grid to window. It will automatically add the reference to WPFToolkit library, and WPFToolkit.dll will be copied to your EXE location.

3.1. But if we not installed WPFToolkit via MSI properly, we shouldn't see in "Toolbox". In this case, we should add controls from library manually.

3.1.1. At first, we should find the WPFToolkit.dll.

If we installed the WPF Tookit, we should go to C:\Program Files (x86)\WPF Toolkit\ (for Win x64) or C:\Program Files\WPF Toolkit\ (for Win x86), and next open v3.5.50211.1 subfolder (or subfolder with different name if it differs). There is your WPFToolkit.dll.

After we found the WPFToolkit.dll, let's copy it to our EXE's location (i.e. \bin\Debug).

3.1.2. Right-click the "Toolbox", and select "Choose Items..." menu item. Now "Choose Toolbox Items" dialog is shown. Let's wait while all WPF components will be loaded. It may take one minute or few.

3.1.3. Click the "Browse..." button and select our WPFToolkit.dll in EXE's location.

3.1.4. Make sure that DataGrid now appears in dialog's listbox and is checked. If it is, click OK - and go to put DataGrid to window.

Binding DataGrid To DataTable

At first, we should change this Winforms code:

dataGridView1.DataSource = dtCustomers;

to this:

dataGrid1.ItemsSource = dtCustomers.DefaultView;

Next. we should consider that WPF's DataGrid by default not generates columns automatically, and by default we will see some like this:

Let's set grid's AutoGenerateColumns property to True, or just reset it (remove from XAML), these operations are equivalent.

Run the project. Now we see that:

That's better. Now it properly displays fields of basic types i.e. Integer and String. But it doesn't display the images.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Rou1997
Europe Europe
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralMy vote of 4 Pin
PalleDue9-May-17 4:22
memberPalleDue9-May-17 4:22 
GeneralRe: My vote of 4 Pin
Rou19979-May-17 5:32
memberRou19979-May-17 5:32 
Suggestiontyped dataset Pin
Mr.PoorEnglish16-Jul-16 4:46
memberMr.PoorEnglish16-Jul-16 4:46 
GeneralDuplicate? Pin
PIEBALDconsult23-Jun-16 17:11
protectorPIEBALDconsult23-Jun-16 17:11 
GeneralRe: Duplicate? Pin
Rou199723-Jun-16 17:15
memberRou199723-Jun-16 17:15 
GeneralMy vote of 5 Pin
PurinT23-Feb-16 15:00
memberPurinT23-Feb-16 15:00 
QuestionConnect to Access file with "Any CPU", 32 bit / 64 bit Pin
Izhar A.18-Dec-15 7:34
memberIzhar A.18-Dec-15 7:34 
AnswerRe: Connect to Access file with "Any CPU", 32 bit / 64 bit Pin
Rou199718-Dec-15 15:12
memberRou199718-Dec-15 15:12 
GeneralRe: Connect to Access file with "Any CPU", 32 bit / 64 bit Pin
Izhar A.19-Dec-15 0:52
memberIzhar A.19-Dec-15 0:52 
GeneralRe: Connect to Access file with "Any CPU", 32 bit / 64 bit Pin
Rou199719-Dec-15 1:54
memberRou199719-Dec-15 1:54 
AnswerRe: Connect to Access file with "Any CPU", 32 bit / 64 bit Pin
Southmountain16-Jul-16 10:23
memberSouthmountain16-Jul-16 10:23 
GeneralRe: Connect to Access file with "Any CPU", 32 bit / 64 bit Pin
Rou199717-Jul-16 0:34
memberRou199717-Jul-16 0:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 31 Aug 2017
Article Copyright 2017 by Rou1997
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid