title: Dynamically Insert single record into MS access from SQL server
body: <p>I have two databases(SQlserver and MS Access) with same schemas (same tables). SQL server database has data but Access has no data (blank database).</p>
  
  <p>My goal : when user enters a ClientId and click insert button then  I need to retrive that single record from all tables in sql server database and insert into tables in MS Access Database.</p>
  
  <p>Achieved:  i retrived from all tables in sql server databases  with client id and stored the data in Dataset.</p>
  
  <p>i have table array and i am looping thru all tables in array and trying to insert data from above dataset into Ms Access dynamically.</p>
  
  <p>Can you suggest how insert into Access for all tables dynamically in loop . i cannot wrie insert statement for each table. i need one which is generic for every table so that i will pass parametrs.  Its not a bulk insert, its single record push into multiple tables.</p>
  
  <p>-------This is My code-------------------------------------------------------</p>
  
  <pre><code>private void InsertMsiClientIntoTest(string ClientId)
          {
              SqlConnection sqlConnection = null;
              SqlDataAdapter sqlDataAdapter = null;
              DataSet sqlserverDataset = new DataSet();
              sqlserverDataset.Tables.Add();
              sqlConnection = new SqlConnection();
              sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;");
              sqlConnection.Open();
              sqlDataAdapter = new SqlDataAdapter(ClientSQL.PopulateTables, sqlConnection);
              sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbId.Text);
             sqlDataAdapter.Fill(sqlserverDataset);
             GetDataFromTablesForID(sqlserverDataset); 
             InsertAllTableDataIntoAccess(sqlserverDataset,tableArray);
          }
  
  
          private DataSet GetDataFromTablesForID(DataSet dsTablesList)
          {
              SqlConnection sqlConnection = null;
              SqlDataAdapter sqlDataAdapter = null;
              string tableName = string.Empty;
              string QueryText = string.Empty;
              int i =0;
              tableArray = new string[dsTablesList.Tables[0].Rows.Count];
              DataSet sqlserverDataset = new DataSet();
              sqlserverDataset.Tables.Add();
              sqlConnection = new SqlConnection();
              sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;");
              sqlConnection.Open();
              foreach (DataRow itemRow in dsTablesList.Tables[0].Rows)
              {                
                   tableArray[i] = itemRow[0].ToString();
                   i++;
              }
              foreach (string tableItem in tableArray)
              {
                  tableName = tableItem;
  
                      QueryText = "select x.*  from" + " " + tableName + " " +
                      "x inner join ClientMajor ci on ci.ClientId = x.ClientId where ci.MajorClientId =@ClientId";
                  }
                  sqlDataAdapter = new SqlDataAdapter(QueryText, sqlConnection);
                  sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbExceedId.Text);
                  sqlDataAdapter.Fill(sqlserverDataset);
              }
  
              return sqlserverDataset;
  
          }
  
          private void InsertAllTableDataIntoAccess(DataSet Inputset, string[] tableArray)
          {
              string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["mdb"].ToString();
              OleDbConnection oledbConnection = null;
              OleDbDataAdapter oledbDataAdapter = null;
              DataSet resultSet = new DataSet();
              oledbConnection = new OleDbConnection(connectionstring);
              oledbConnection.Open();
              foreach (string tableItem in tableArray)
              {
  //?????????  I NEED THE FOLLOWING CODE......////////////???????           
                  oledbDataAdapter.InsertCommand.CommandText="insert into "
                  oledbDataAdapter.Fill(resultSet.Tables[0]);
              }
          }
  </code></pre>
  
  <p>Thanks in Advance...</p>