Read Microsoft Access Database in C#

In this article, you’ll learn how to connect and read a Microsoft Access 2003 or earlier versions (.mdb).

Note: Oritinally, this article was published on Jan 01, 2000. This is an updated article.

The code snuppet in this aricle is a simple console application that connects with an Access 2000 database, read data from a table, and displays it on the console. The database has a table named, Developer with two columns, Name and Address.

.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 Sustem.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 C# console app that connects with an Access database, opens a connection, reads data, and displays it on the system console.

  1. using System;  
  2. using System.Data.OleDb;  
  3. namespace AccessDBSample  
  4. {  
  5. class Program  
  6. {  
  7. static void Main(string[] args)  
  8. {  
  9. // Connection string and SQL query  
  10. string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Mahesh\Data\Dev.mdb";  
  11. string strSQL = "SELECT * FROM Developer";  
  12. // Create a connection  
  13. using (OleDbConnection connection = new OleDbConnection(connectionString))  
  14. {  
  15. // Create a command and set its connection  
  16. OleDbCommand command = new OleDbCommand(strSQL, connection);  
  17. // Open the connection and execute the select command.  
  18. try  
  19. {  
  20. // Open connecton  
  21. connection.Open();  
  22. // Execute command  
  23. using (OleDbDataReader reader = command.ExecuteReader())  
  24. {  
  25. Console.WriteLine("------------Original data----------------");  
  26. while (reader.Read())  
  27. {  
  28. Console.WriteLine("{0} {1}", reader["Name"].ToString(), reader["Address"].ToString());  
  29. }  
  30. }  
  31. }  
  32. catch (Exception ex)  
  33. {  
  34. Console.WriteLine(ex.Message);  
  35. }  
  36. // The connection is automatically closed becasuse of using block.  
  37. }  
  38. Console.ReadKey();  
  39. }  
  40. }  
  41. }  

The following code snippet adds a new row in the table. The INSERT SQL query is used to add a new row.

  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