title: How to Export Data from SQL server Compact To Access MDB
body: <p>I need a solution to transfer all data from SQL Server CE to Access mdb database.</p>
  
  <p>I tried this approach <a href="http://www.codeproject.com/Answers/483989/HowplustoplusExportplusSQLplusTablesplusToplusAcce#answer2" rel="nofollow">http://www.codeproject.com/Answers/483989/HowplustoplusExportplusSQLplusTablesplusToplusAcce#answer2</a> (solution # 2) but getting an error "No database specified in connection string or IN clause."</p>
  
  <p>The code works if I connect to non-compact SQL server.</p>
  
  <p>I guess the problem is with connection string in IN clause but I cannot figure out how to change it.</p>
  
  <p>Here is my code:</p>
  
  <pre><code>private void ExportTable(string tableName, string source, string destination)
  {
      var connStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", destination);
      var cmdText = string.Format("SELECT * INTO {0} FROM [{0}] IN ''[Data Source={1};Max Database Size='4000';Persist Security Info=False;];", tableName, source);
  
      using (var conn = new OleDbConnection(connStr))
      {
              conn.Open();
              using (var cmd = new OleDbCommand(cmdText, conn))
              {
                  cmd.ExecuteNonQuery(); // error on this line
              }
  
              conn.Close();
      }
  }
  </code></pre>
  
  <p>The connection string: Data Source={1};Max Database Size='4000';Persist Security Info=False; working ok when I connect to the database directly.</p>
  
  <p><strong>UPDATE:</strong> Apparently the format of the source DB in IN Clause should be as following:<br>
   [<em>type</em>; DATABASE = <em>path</em>]</p>
  
  <p>(see: <a href="http://answers.microsoft.com/en-us/office/forum/office_2010-access/access-2010-runtime-error-3170-could-not-find/0b085797-618a-488f-b1b4-30af00f04b3f" rel="nofollow">http://answers.microsoft.com/en-us/office/forum/office_2010-access/access-2010-runtime-error-3170-could-not-find/0b085797-618a-488f-b1b4-30af00f04b3f</a>)</p>
  
  <p>When I use </p>
  
  <pre><code>var cmdText = string.Format("SELECT * INTO {0} FROM [{0}] IN ''[SqlServer CE; DATABASE={1}];", tableName, source);
  </code></pre>
  
  <p>I am getting different error: Could not find installable ISAM.</p>
  
  <p>Do you know correct <em>type</em> for SQLServer CE? Is it supported at all? I could not find any info about it.</p>
  
  <p>I have also tried: SQL CE, SQLSERVER.CE, Microsoft.SQLSERVER.CE.OLEDB.3.5, Microsoft.SQLSERVER.MOBILE.OLEDB.3.0 etc. - Same error...</p>