利用程式讀取Access資料

這篇主要是整理如何利用程式連接Access檔案，以下為微軟針對OleDb、Odbc範例

OleDb

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

class Program
{
  static void Main()
  {
      string connectionString = GetConnectionString();
      string queryString =
          "SELECT CategoryID, CategoryName FROM Categories;";
      using (OleDbConnection connection =
                 new OleDbConnection(connectionString))
      {
          OleDbCommand command = connection.CreateCommand();
          command.CommandText = queryString;

          try
          {
              connection.Open();

              OleDbDataReader reader = command.ExecuteReader();

              while (reader.Read())
              {
                  Console.WriteLine("\t{0}\t{1}",
                      reader[0], reader[1]);
              }
              reader.Close();
          }
          catch (Exception ex)
          {
              Console.WriteLine(ex.Message);
          }
      }
  }

  static private string GetConnectionString()
  {
      // To avoid storing the connection string in your code,
      // you can retrieve it from a configuration file.
      // Assumes Northwind.mdb is located in the c:\Data folder.
      return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
          + "c:\\Data\\Northwind.mdb;User Id=admin;Password=;";
  }
}

Odbc

using System;
using System.Data;
using System.Data.Odbc;

class Program
{
   static void Main()
   {
       string connectionString = GetConnectionString();
       string queryString =
           "SELECT CategoryID, CategoryName FROM Categories;";
       using (OdbcConnection connection =
                  new OdbcConnection(connectionString))
       {
           OdbcCommand command = connection.CreateCommand();
           command.CommandText = queryString;

           try
           {
               connection.Open();

               OdbcDataReader reader = command.ExecuteReader();

               while (reader.Read())
               {
                   Console.WriteLine("\t{0}\t{1}",
                       reader[0], reader[1]);
               }
               reader.Close();
           }
           catch (Exception ex)
           {
               Console.WriteLine(ex.Message);
           }
       }
   }

   static private string GetConnectionString()
   {
       // To avoid storing the connection string in your code,
       // you can retrieve it from a configuration file.
       // Assumes Northwind.mdb is located in the c:\Data folder.
       return "Driver={Microsoft Access Driver (*.mdb)};"
           + "Dbq=c:\\Data\\Northwind.mdb;Uid=Admin;Pwd=;";
   }
}

另外微軟針對Office 2007 Access檔案格式有進行修改，如使用的資料來源為2007格式，可參考wiki連線範例

Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\myFolder\myAccess2007file.accdb;
Persist Security Info=False;

相關連結

http://msdn.microsoft.com/zh-tw/library/dw70f090(v=vs.80).aspx

http://zh.wikipedia.org/wiki/Microsoft_Jet_Database_Engine