title: Copy table from SQL Server 2008 to MS Access 2007
body: <p>Firstly, For Those who would like to ask WHY on earth am I DOWNSIZING from SQL SERVER to ACCESS, let me tell you the scenario.
  There are some PC's with very low configuration, (256 MB RAM, 2GHzProcessor) I cannot install SQL Server. Hence I want major operations to carry out on SQL server and some data retrieving and auditing work to be done on Slower machine.</p>
  
  <p>So here we go:
  I want to copy table from SQL Server to MS Access 2007. I tried:</p>
  
  <p>1)Connect to sql server, fill a datatable object by reading table from sql server.</p>
  
  <p>2) Create a connection to MS Access, and use Dataadapter.Update method to update table to MS Access database.</p>
  
  <p>However 2nd step is not working although its not throwing any error. Here is my code:</p>
  
  <pre><code>SqlConnection cnn = new SqlConnection(@"initial catalog=DBTempleERM;user id=aditya;password=Aditya_ravi$;Data Source=adityalappy\sqlexpress");
  SqlCommand cmd = new SqlCommand("SELECT * FROM donationdetails", cnn);
  cnn.Open();
  System.Data.SqlClient.SqlDataAdapter sDA = new SqlDataAdapter(cmd);
  DataTable donationdetails = new DataTable();
  sDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
  sDA.Fill(donationdetails);
  
  MessageBox.Show(donationdetails.Rows.Count.ToString());
  
  OleDbConnection oleConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=Aditya_ravi$;Data Source=C:\dbt.accdb");
  oleConn.Open();
  OleDbCommand oleComm = new OleDbCommand();
  OleDbDataAdapter oDA = new OleDbDataAdapter(oleComm);
  OleDbCommandBuilder oCb = new OleDbCommandBuilder(oDA);
  oDA.Update(donationdetails);
  </code></pre>
  
  <p>No error is thrown at the end of the execution, but I cannot see any records copied from SQL Server to MS Access.</p>
  
  <p>I learnt that SQL Bulk copy cannot be used to copy from SQL Server to Access.</p>
  
  <p>I also want to add the primary key from SQL Server table to MS Access table.</p>