我在.NET中找到了一个令我非常满意的解决方案.它使我可以将同步例程的访问权限授予程序中的任何用户.它涉及SQLBulkCopy类.

I found a solution in .NET that I am very happy with. It allows me to give the access to the sync routine to any user within my program. It involves the SQLBulkCopy class.

private static void BulkCopyAccessToSQLServer
        (CommandType commandType, string sql, string destinationTable)
    {
        using (DataTable dt = new DataTable())
        {
            using (OleDbConnection conn = new OleDbConnection(Settings.Default.CurriculumConnectionString))
            using (OleDbCommand cmd = new OleDbCommand(sql, conn))
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
            {
                cmd.CommandType = commandType;
                cmd.Connection.Open();
                adapter.SelectCommand.CommandTimeout = 240;
                adapter.Fill(dt);
                adapter.Dispose();
            }

            using (SqlConnection conn2 = new SqlConnection(Settings.Default.qlsdat_extensionsConnectionString))
            {
                conn2.Open();
                using (SqlBulkCopy copy = new SqlBulkCopy(conn2))
                {
                    copy.DestinationTableName = destinationTable;
                    copy.BatchSize = 1000;
                    copy.BulkCopyTimeout = 240;
                    copy.WriteToServer(dt);
                    copy.NotifyAfter = 1000;
                }
            }
        }
    }

基本上，这会将来自MS Access的数据放入数据表，然后使用第二个连接conn2和SqlBulkCopy类将数据从该数据表发送到SQL Server.它可能不是***的代码，但是应该让任何人读懂这个主意.

Basically this puts the data from MS Access into a DataTable it then uses the second connection conn2 and the SqlBulkCopy class to send the data from this DataTable to the SQL Server. It's probably not the best code but should give anyone reading this the idea.