Tuesday, March 4, 2014

MS. Access connection with C# in windows or web application

Ms. Access is very useful database for the standalone application, where you need to run the program without installing the database in user's machine like a quiz or any other examination system. So lets have a look how to code the program to connect your software with Ms. Access database.

If you have done previously to connect the SQL server and done fetching or retrieving data from database it is more or less same for you. Everything is same except the connection string.

The Ms. Access connection string is here for you, with example of how to run a query(get or insert) in web application or windows application using of  C#.

You must hear about the OleDbConnection in .NET, we are going to use this one to connect your Ms. Access database.

OleDbConnection conn = new OleDbConnection();
            String connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\Quiz.accdb;Persist Security Info=True";
                conn.ConnectionString = connection;
                DataTable ds = new DataTable();
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                return ds;
            catch (Exception ae)

This one means the address of the database. "DataDirectory" means where the application is running, i.e. Bin->Debug and within Debug the access file is present named Quiz.accdb. "accdb" is the extension of Ms. Access database.

Here a string is working named "sql". You can pass any query whether its for fetch or insert it will work same.

Now here is a small demo to create database in Ms. Access.

  1. Open Ms. Access from Ms. office
  2. Choose a blank database and enter your desirable name from File name and click on Create button
  3. Automatic one table is created named Table1, now choose the table rows and add/rename according to your requirement.
  4. Now save it by clicking Ctrl+S or go to File->Save to save the table. Here you can change the table name. 
  5. Like this add as much table as you want.
Now your database is ready as well as the program. So code it and enjoy.


Post a Comment

Popular Posts