Bulk copy from MS Access to SQL Server

Question

I'm trying to copy tables form a MS Access database into a SQL Server database.

This is the code:

private static void BulkCopyAccessToSQLServer(string sql, string destinationTable,string fileName)
{
  using (DataTable dt = new DataTable())
  {
     using (OleDbConnection conn = new OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", fileName)))
     using (OleDbCommand cmd = new OleDbCommand(sql, conn))
     using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
     {
         cmd.Connection.Open();
         adapter.SelectCommand.CommandTimeout = 240;
         adapter.Fill(dt);
         adapter.Dispose();
     }
     using (SqlConnection conn2 = new SqlConnection(connStr))
     {
         conn2.Open();
         using (SqlBulkCopy copy = new SqlBulkCopy(conn2))
         {
             copy.DestinationTableName = destinationTable;
             copy.BatchSize = 1000;
             copy.BulkCopyTimeout = 240;
             copy.WriteToServer(dt);
             copy.NotifyAfter = 1000;
         }
     }
  }
}

Calling this function like this...

BulkCopyAccessToSQLServer("select * from Table1", "Table1", "C:\\db.‌accdb");

I get the following error:

System.Data.OleDb.OleDbException: Could not find file 'C:\db.‌accdb'.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at WebService.BulkCopyAccessToSQLServer(String sql, String destinationTable, String fileName)
at WebService.Import()

What am I doing wrong?

ive done this through sql management but need to do it through my code — Beginner
could you tell me how to schedule it through management studio then? — Beginner

Guvante Guvante · Accepted Answer · 2012-02-22T16:51:39

You are running the bulk import from a web service from the looks of your stack trace.

Does the web service process have sufficient rights to access C:\db.accdb?

Bulk copy from MS Access to SQL Server

1 Answers