ADO.NET

Read Microsoft Access Database in C#

In this article, you’ll learn how to connect and read Microsoft Access 2003 or earlier versions (.mdb).
 
Note: Originally, this article was published on Jan 01, 2000. This is an updated article.
 
The code snippet in this article is a simple console application that connects with an Access 2000 database, reads data from a table, and displays it on the console. The database has a table named, Developer with two columns, Name and Address.
 
The .NET framework has two common approaches, ADO.NET and LINQ to read databases. ADO.NET uses the OLE-DB data provider to read a Microsoft Access database. The OLE-DB classes are defined in the System.Data.OleDb namespace. We must import the System.Data.OleDb namespace in our code using the following definition.
  1. using System.Data.OleDb; 
The following code is the complete list of the C# console app that connects with an Access database, opens a connection, reads data, and displays it on the system console.
 
The following code snippet adds a new row in the table. The INSERT SQL query is used to add a new row.
  1. using System;  
  2. using System.Data.OleDb;  
  3. namespace AccessDBSample {  
  4.     class Program {  
  5.         static void Main(string[] args) {  
  6.             // Connection string and SQL query    
  7.             string connectionString = @ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Mahesh\Data\Dev.mdb";  
  8.             string strSQL = "SELECT * FROM Developer";  
  9.             // Create a connection    
  10.             using(OleDbConnection connection = new OleDbConnection(connectionString)) {  
  11.                 // Create a command and set its connection    
  12.                 OleDbCommand command = new OleDbCommand(strSQL, connection);  
  13.                 // Open the connection and execute the select command.    
  14.                 try {  
  15.                     // Open connecton    
  16.                     connection.Open();  
  17.                     // Execute command    
  18.                     using(OleDbDataReader reader = command.ExecuteReader()) {  
  19.                         Console.WriteLine("------------Original data----------------");  
  20.                         while (reader.Read()) {  
  21.                             Console.WriteLine("{0} {1}", reader["Name"].ToString(), reader["Address"].ToString());  
  22.                         }  
  23.                     }  
  24.                 } catch (Exception ex) {  
  25.                     Console.WriteLine(ex.Message);  
  26.                 }  
  27.                 // The connection is automatically closed becasuse of using block.    
  28.             }  
  29.             Console.ReadKey();  
  30.         }  
  31.     }  
  32. } 
  1. // Add a new row  
  2. strSQL = "INSERT INTO Developer(Name, Address) VALUES ('New Developer', 'New Address')";  
  3. command = new OleDbCommand(strSQL, connection);  
  4. // Execute command  
  5. command.ExecuteReader();  
The following code snippet updates rows that match with the WHERE condition in the query.
  1. // Update rows  
  2. strSQL = "UPDATE Developer SET Name = 'Updated Name' WHERE Name = 'New Developer'";  
  3. command = new OleDbCommand(strSQL, connection);  
  4. command.ExecuteReader();  
The following code snippet deletes rows that match with the WHERE condition.
  1. // Delete rows  
  2. strSQL = "DELETE FROM Developer WHERE Name = 'Updated Name'";  
  3. command = new OleDbCommand(strSQL, connection);  
  4. command.ExecuteReader();  
Note: Access 2013 or Access 2016 versions work differently. If you want to work with Access 2013, 2016 or Office 365 databases, visit this article: Connecting C# Application To MS Access Database

Up Next
    Ebook Download
    View all
    Learn
    View all
    Founded in 2003, Mindcracker is the authority in custom software development and innovation. We put best practices into action. We deliver solutions based on consumer and industry analysis.
    Membership not found