For some reason I must back to code in C# for a while, the problem I just have to solve is how to connect my C# application to my Access Database. I have been search and found many solution out there and today I want to share step by step how easy it to be done. Here we go 😉 . At first simply create Access database (e.g. book.mdb) like picture below, their column name and data type is ID (Autonumber), bookName (Text), description (Text).
After that save your database and create connection parameter which will be use in the code. Now open notepad.exe , then simply save as it to mdsaputra.udl , don’t forget to change Save as type into All Files (*.*).
Now close your notepad and double click mdsaputra.udl which you just created. You gonna see wizard like picture below :
Click Provider Tab (1), select Microsof Jet 4.0 ILE DB Provider (2) then click Next (3). Now you are in Connection Tab.
Click … /browse button (1) , select book.mdb (2), open (3) and then click Test Connection, if you do everything properly you must see Test Connection Succeed like picture below :
Click OK, and now reopen mdsaputra.udl with notepad, you gonna see some provider properties like shown below :
Text I highlight above is provider properties that we gonna need in our code, for now just close it. We are done with database and it connection things, now open your Visual Studio and create Visual C#, Windows Forms Application. Fill and arrange your form with Label, TextBox, Button and DataGridView like shown below.
Double click your Insert Button and Show All button to auto create Event Handler method. Now, inside your Form.cs add code that highlighted and shown below :
//author EtaYuy | mdsaputra.wordpress.com | Meihta Dwiguna Saputra 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 TutorialConnectToAccessDB { public partial class FormMain : Form { private OleDbConnection bookConn; private OleDbCommand oleDbCmd = new OleDbCommand(); //parameter from mdsaputra.udl private String connParam = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\EtaYuy\Documents\book.mdb;Persist Security Info=False"; public FormMain() { //create connection using parameter from mdsaputra.udl bookConn = new OleDbConnection(connParam); InitializeComponent(); } private void buttonInsert_Click(object sender, EventArgs e) { bookConn.Open(); oleDbCmd.Connection = bookConn; oleDbCmd.CommandText = "insert into book (bookName, description) values ('" + this.textBoxBookName.Text + "','" + this.textBoxDescription.Text + "');"; int temp = oleDbCmd.ExecuteNonQuery(); if (temp > 0) { textBoxBookName.Text = null; textBoxDescription.Text = null; MessageBox.Show("Record Successfuly Added"); } else { MessageBox.Show("Record Fail to Added"); } bookConn.Close(); } private void buttonShowAll_Click(object sender, EventArgs e) { dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dataGridView1.Refresh(); OleDbDataAdapter dAdapter = new OleDbDataAdapter("select * from book", connParam); OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter); DataTable dataTable = new DataTable(); DataSet ds = new DataSet(); dAdapter.Fill(dataTable); for (int i = 0; i < dataTable.Rows.Count; i++) { dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2]); } } } }
See highlighted code above, that connParameter assigned by provider properties from mdsaputra.udl. Now everything is done, if you following my instruction carefully, the program should be able Insert new Record into book.mdb like shown below,
and also can show all data you have been insert into book.mdb like picture below.
Well, that’s all, hope the tutorial easy to understand, thanks for reading ;). You can download my working copy here.
Subscribe via twitter : Follow @etayuy
sameer jadhav
Apr 17, 2012 @ 13:56:26
we tried this code but the grid part is not working …..it is showing an error like this:
No row can be added to a DataGridView control that does not have columns. Columns must be added first.
Meihta Dwiguna Saputra
Apr 17, 2012 @ 15:44:33
rite, don’t forget to adding column to your table or it wont work, dont just make a table, but add columns and columns title first
wina
Jul 02, 2012 @ 15:44:21
its not working. it says the “using linq” is not used
Pratik Goyal
Sep 21, 2012 @ 23:33:15
It says the following error-
“The ‘Microsoft.Jet.OLEDB.12.0’ provider is not registered on the local machine.”
samson
Feb 18, 2013 @ 20:40:10
Am sure you are using a 64bit operating systen so change the properties from x64 to x86
joseph
Apr 27, 2013 @ 12:19:21
how to change it?
Dhanashri
Mar 03, 2014 @ 01:12:59
right click on project name in solution explorer..then click properties…open tab build change platform target from x64 to x86
suzi
Mar 17, 2013 @ 00:25:57
how to save an image, to the database? the field name: FaceImage, datatype: OLE object.
i use pictureBox to display it in windows form. Please help..
Meihta Dwiguna Saputra
Mar 22, 2013 @ 14:13:13
you can save it as blob, then convert from byte[] to image before load it to your pictureBox 🙂
Sammie Asava
Apr 03, 2013 @ 22:08:22
Here is a database compatible with .NET, Silverlight, Windows Phone, Mono, Monodroid, and Monotouch:
http://www.kellermansoftware.com/p-43-ninja-net-database-pro.aspx
Hidayat
Jul 02, 2013 @ 14:36:44
how to view Ms Acess Reports in C sharp ado.net not CoM or Automation
Salman
Jul 19, 2013 @ 00:25:05
Poor mouse !
Yousuf
Sep 01, 2013 @ 13:15:34
Oh my friend
I was using several method for this, but your lesson for connecting Access with C# is more convenient.
Please continue this effort, to teach people.
thank you again
Anokha
Sep 05, 2013 @ 09:43:32
I have tried this it is working. Thanks
Tomasz
Oct 31, 2013 @ 18:02:50
Hi
If you work on Widows 7 64 bit forget about Microsoft Jet.
In Class FormMain write : @”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=……(your patch to the access database)
Thats work.
Man, you really helped me.
Greetings from Poland 🙂
ali
Nov 08, 2013 @ 00:35:47
kindly mention the update query of above scenairo becoz i m facing a problem in update query
karthik
Nov 11, 2013 @ 13:06:29
Got it Working…..!
NAGARAJ
Nov 23, 2013 @ 04:11:06
Thank you……….
IT WORKS 100%
Vilas Shende
Feb 04, 2014 @ 15:17:50
Hi, I am using Office 2007, While creating database file its extention goes to accdb instead of mdb, well I created database file & text (notepad) file as suggested & bros database file also but when I trying to test connection it is giving error. please suggest what I should do to get it done?
Meihta Dwiguna Saputra
Feb 05, 2014 @ 21:37:44
ever trying to save it as mdb?
Mahesh
Feb 15, 2014 @ 17:32:14
This ie very nice article . Help me lot
krunal
Mar 06, 2014 @ 21:42:11
Microsof Jet 4.0 ILE DB Provider is not avilable on my local machine what i should do
hobittect
Mar 24, 2014 @ 16:37:15
Microsoft Jet 4.0 ILE DB Provider is not show on my data link properties tab
what can i do ??
tulika
May 05, 2014 @ 03:07:45
thanks…..very nice article…
Kết nối cơ sở dữ liệu với Access trong C# | ► Trang chủ
May 20, 2014 @ 17:22:44
Prince
Jul 16, 2014 @ 13:52:29
Thank
prem
Aug 07, 2014 @ 09:09:18
Microsoft Jet 4.0 ILE DB Provider is not show on my data link properties tab
what can i do ??
siva
Sep 11, 2014 @ 14:23:22
this can be done in windows xp?????????????????????????
Faizan
Sep 15, 2014 @ 16:18:20
Thank u thank uuuuuu so muchhhh 🙂
amar
Sep 16, 2014 @ 18:31:50
datatype mismatch error
phanny
Oct 17, 2014 @ 08:57:38
Why I cannot connect?
williamholdin
Dec 08, 2014 @ 14:10:19
Check this one…C# Database programming for beginners
William
hrs
Feb 22, 2015 @ 16:30:42
lalalala
C# DEVELOPER
Feb 28, 2015 @ 01:18:04
GREAT TUTORIAL ….. WORKING 100% …… THANX 4R THIS.. AND KEEP IT UP
gaurav
Jun 11, 2015 @ 14:56:48
using windows 7 64bit microsoft jet 4.0 ile db provider is not shown what to do
Gerald Lois
Nov 17, 2015 @ 15:59:32
It’s really interesting article
Akhil
Nov 18, 2015 @ 00:10:43
Microsoft Jet 4.0 ILE DB Provider is not show
afieee
Feb 16, 2016 @ 16:33:22
lolololoolloloo
Rodel
Feb 24, 2016 @ 13:23:37
I’m a VB.net programmer, Now I want to learn C# programming, These tutorial really helps me a lot. ^_^
Bindiya Vaja
Feb 25, 2016 @ 13:03:18
how to write update code in c#… help…
Miley
May 14, 2016 @ 12:08:02
Microsoft jet 4.0 is not there in the provider list…plz suggest me what should i do????
How To Connect C # With Access Database | historydeal
Jun 04, 2016 @ 16:19:50
How To Connect C Program To Access Database | historydeal
Jul 01, 2016 @ 04:16:20
Dev Desai
Mar 14, 2017 @ 05:26:00
I loved this approach of udl. It was gr8
bangaloretrekkers
May 10, 2017 @ 19:04:16
Thanks, This helped me and it indeed worked like charm