For my current project in c# I have to transfer data from the SQL-Database for an Access-Database. For the time being I load the information right into a DataSet utilizing a SqlDataAdapter. Next I loop with the records and place them in to the Access-DB using OleDb:

// Load data from SQL
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter("select goes here", sqlConnection);
adapter.Fill(ds);

// Prepare the Insert Command
oleDBCommand = "Insert into...";
oleDBCommand.Parameters.Add(new OleDbParameter(...));

// Insert every row from the DataSet
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
    // Update Parameters and Execute
    oleDBCommand.Parameters[0].Value = ds.Tables[0].Rows[i].ItemArray[0];
    oleDBCommand.ExecuteNonQuery();
}

This method works fine, nevertheless it feels clumsy and slow. So I'm wondering if there's another better method to transfer data in one DB to a different.

  1. Use SqlDataReader: SqlDataReader runs faster than SqlDataAdapter
  2. Use Transaction: make use of a transaction and bind each command for this transaction. after finishing the place instructions, commit the transaction, which might improve your speed.

Also try this:
Whether it's always exactly the same Access database and also you import your computer data always in the same table(s), you are able to link the SQL Server tables in Access once.

If your SQL Server table is related, technology-not only within the MDB as being a local table.
Then, you can easily place from the linked table in to the local table by running this question via OleDB within the Access database:

insert into LocalAccessTable (Column1, Column2)
select Column1, Column2
from LinkedSqlServerTable