title: How do I structure an OleDbCommand Query so that I can take Tables from one .MDB, and replace them in another .MDB
body: <p>I am trying to take tables from one Access Database File, add them to another Access Database file with the exact same structure but with different information. I need to overwrite any existing tables. I am almost done with my project this is last my brick wall.</p>
  
  <p>I am using a separate class file named DatabaseHandling.cs to work with the Access Database files.</p>
  
  <p>Here is my ENTIRE current DatabaseHandling.cs code. This is kept up to date for now on.</p>
  
  <p>Code:</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.OleDb;
  using System.IO;
  using System.Linq;
  using System.Text;
  
  namespace LCR_ShepherdStaffupdater_1._0
  {
      public class DatabaseHandling
      {
          static DataTable datatableB = new DataTable();
          static DataTable datatableA = new DataTable();
          public static DataSet datasetA = new DataSet();
          public static DataSet datasetB = new DataSet();
          static OleDbDataAdapter adapterA = new OleDbDataAdapter();
          static OleDbDataAdapter adapterB = new OleDbDataAdapter();
          static string connectionstringA = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Settings.getfilelocationA();
          static string connectionstringB = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Settings.getfilelocationB();
          static OleDbConnection dataconnectionB = new OleDbConnection(connectionstringB);
          static OleDbConnection dataconnectionA = new OleDbConnection(connectionstringA);
          static DataTable tableListA;
          static DataTable tableListB;
  
          static public void addTableA(string table, bool addtoDataSet)
          {
              dataconnectionA.Open();
              datatableA = new DataTable(table);
              try
              {
                  OleDbCommand commandselectA = new OleDbCommand("SELECT * FROM [" + table + "]", dataconnectionA);
                  adapterA.SelectCommand = commandselectA;
                  adapterA.Fill(datatableA);
              }
              catch
              {
                  Logging.updateLog("Error: Tried to get " + table + " from DataSetA. Table doesn't exist!", true, false, false);
              }
  
              if (addtoDataSet == true)
              {
                  datasetA.Tables.Add(datatableA);
                  Logging.updateLog("Added DataTableA: " + datatableA.TableName.ToString() + " Successfully!", false, false, false);
              }
  
              dataconnectionA.Close();
          }
  
          static public void addTableB(string table, bool addtoDataSet)
          {
              dataconnectionB.Open();
              datatableB = new DataTable(table);
  
              try
              {
                  OleDbCommand commandselectB = new OleDbCommand("SELECT * FROM [" + table + "]", dataconnectionB);
                  adapterB.SelectCommand = commandselectB;
                  adapterB.Fill(datatableB);
              }
              catch
              {
                  Logging.updateLog("Error: Tried to get " + table + " from DataSetB. Table doesn't exist!", true, false, false);
              }
  
  
  
              if (addtoDataSet == true)
              {
                  datasetB.Tables.Add(datatableB);
                  Logging.updateLog("Added DataTableB: " + datatableB.TableName.ToString() + " Successfully!", false, false, false);
              }
  
              dataconnectionB.Close();
          }
  
          static public string[] getTablesA(string connectionString)
          {
              dataconnectionA.Open();
              tableListA = dataconnectionA.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
              string[] stringTableListA = new string[tableListA.Rows.Count];
  
              for (int i = 0; i &lt; tableListA.Rows.Count; i++)
              {
                  stringTableListA[i] = tableListA.Rows[i].ItemArray[2].ToString();
              }
              dataconnectionA.Close();
              return stringTableListA;
          }
  
          static public string[] getTablesB(string connectionString)
          {
              dataconnectionB.Open();
              tableListB = dataconnectionB.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
              string[] stringTableListB = new string[tableListB.Rows.Count];
  
              for (int i = 0; i &lt; tableListB.Rows.Count; i++)
              {
                  stringTableListB[i] = tableListB.Rows[i].ItemArray[2].ToString();
              }
              dataconnectionB.Close();
              return stringTableListB;
          }
  
          static public void createDataSet()
          {
  
              string[] tempA = getTablesA(connectionstringA);
              string[] tempB = getTablesB(connectionstringB);
              int percentage = 0;
              int maximum = (tempA.Length + tempB.Length);
  
              Logging.updateNotice("Loading Tables...");
              Logging.updateLog("Started Loading File A", false, true, false);
              for (int i = 0; i &lt; tempA.Length ; i++)
              {
                  if (!datasetA.Tables.Contains(tempA[i]))
                  {
                      addTableA(tempA[i], true);
                      percentage++;
                      Logging.loadStatus(percentage, maximum);
                  }
                  else
                  {
                      datasetA.Tables.Remove(tempA[i]);
                      addTableA(tempA[i], true);
                      percentage++;
                      Logging.loadStatus(percentage, maximum);
                  }
              }
              Logging.updateLog("Finished loading File A", false, true, false);
              Logging.updateLog("Started loading File B", false, true, false);
              for (int i = 0; i &lt; tempB.Length ; i++)
              {
                  if (!datasetB.Tables.Contains(tempB[i]))
                  {
                      addTableB(tempB[i], true);
                      percentage++;
                      Logging.loadStatus(percentage, maximum);
                  }
                  else
                  {
                      datasetB.Tables.Remove(tempB[i]);
                      addTableB(tempB[i], true);
                      percentage++;
                      Logging.loadStatus(percentage, maximum);
                  }
              }
              Logging.updateLog("Finished loading File B", false, true, false);
  
  
          }
  
          static public DataTable getDataTableA()
          {
              datatableA = datasetA.Tables[Settings.textA];
  
              return datatableA;
          }
          static public DataTable getDataTableB()
          {
              datatableB = datasetB.Tables[Settings.textB];
              return datatableB;
          }
  
          static public DataSet getDataSetA()
          {
              return datasetA;
          }
  
          static public DataSet getDataSetB()
          {
              return datasetB;
          }
  
          static public void InitiateCopyProcessA()
          {
              DataSet tablesA;
              tablesA = DatabaseHandling.getDataSetA();
  
                  foreach (DataTable table in tablesA.Tables)
                  {
                      OverwriteTable(table, table.TableName);
                      Logging.updateLog("Copied " + table.TableName + " successfully.", false, true, false);
                  }
  
          }
  
          static void OverwriteTable(DataTable sourceTable, string tableName)
          {
              using (var destConn = new OleDbConnection(connectionstringA))
              using (var destCmd = new OleDbCommand(tableName, destConn) { CommandType = CommandType.TableDirect })
              using (var destDA = new OleDbDataAdapter(destCmd))
              {
                  // Since we're using a single table, we can have the CommandBuilder
                  // generate the appropriate INSERT and DELETE SQL statements
                  using (var destCmdB = new OleDbCommandBuilder(destDA))
                  {
                      destCmdB.QuotePrefix = "["; // quote reserved column names
                      destCmdB.QuotePrefix = "]";
                      destDA.DeleteCommand = destCmdB.GetDeleteCommand();
                      destDA.InsertCommand = destCmdB.GetInsertCommand();
  
                      // Get rows from destination, and delete them
                      var destTable = new DataTable();
                      destDA.Fill(destTable);
                      foreach (DataRow dr in destTable.Rows)
                      {
                          dr.Delete();
                      }
                      destDA.Update(destTable);
  
                      // Set rows from source as Added, so the DataAdapter will insert them
                      foreach (DataRow dr in sourceTable.Rows)
                      {
                          dr.SetAdded();
                      }
                      destDA.Update(sourceTable);
                  }
              }
          }
  
  
  
          }          
      }
  </code></pre>
  
  <p>I simply want to take a Datatable that is in memory and write it to a .MDB file. I have been attempting to do this for over 30 hours.</p>
  
  <p><strong>LATEST EDIT:</strong></p>
  
  <p>Okay, added new code. I get a new run-time error: <strong>Syntax error in FROM clause.</strong></p>
  
  <p><strong>Code:</strong></p>
  
  <pre><code>static public void InitiateCopyProcessA()
  {
      DataSet tablesA;
      tablesA = DatabaseHandling.getDataSetA();
  
          foreach (DataTable table in tablesA.Tables)
          {
              OverwriteTable(table, table.TableName);
              Logging.updateLog("Copied " + table.TableName + " successfully.", false, true, false);
          }
  
  }
  
  static void OverwriteTable(DataTable sourceTable, string tableName)
  {
      using (var destConn = new OleDbConnection(connectionstringA))
      using (var destCmd = new OleDbCommand(tableName, destConn) { CommandType = CommandType.TableDirect })
      using (var destDA = new OleDbDataAdapter(destCmd))
      {
          // Since we're using a single table, we can have the CommandBuilder
          // generate the appropriate INSERT and DELETE SQL statements
          using (var destCmdB = new OleDbCommandBuilder(destDA))
          {
              destCmdB.QuotePrefix = "["; // quote reserved column names
              destCmdB.QuotePrefix = "]";
              destDA.DeleteCommand = destCmdB.GetDeleteCommand();
              destDA.InsertCommand = destCmdB.GetInsertCommand();
  
              // Get rows from destination, and delete them
              var destTable = new DataTable();
              destDA.Fill(destTable);
              foreach (DataRow dr in destTable.Rows)
              {
                  dr.Delete();
              }
              destDA.Update(destTable);
  
              // Set rows from source as Added, so the DataAdapter will insert them
              foreach (DataRow dr in sourceTable.Rows)
              {
                  dr.SetAdded();
              }
              destDA.Update(sourceTable); // !!! Run-time error: Syntax error in FROM clause. !!!
          }
      }
  }
  </code></pre>
  
  <p>Once again, it does not work. Let me know if you need additional information.</p>