How to copy Access table to SQL table in C#?

As title says I’ve used odbcconnection to sqlconnection and for the life of me cant get it to work.. Copied a bunch of code from this site but cant get them both to work.

The program just hangs so maybe I am doing something wrong, but would appreciate maybe a bare bones template that i could just fill in the connection details and bulk copy the table to table..

 using (OdbcConnection myConnection = new OdbcConnection())
            {
                string myConnectionString = @"Driver={Microsoft Access Driver (*.mdb)};" +
 "Dbq=//####/data/Toronto/wrkgrp/wrkgrp30/Business Risk Oversight and Control/DATA INTEGRITY/CDE/CDE Testing Portal Requirements/Migration Requirements/RCM/Mutual Funds/Mutual Funds.mdb;";

                myConnection.ConnectionString = myConnectionString;
                myConnection.Open();

                //execute queries, etc
                OdbcCommand cmd = myConnection.CreateCommand();

                cmd.CommandText = "SELECT * FROM RCM_MF_New_Accounts_Samples";
                OdbcDataReader reader = cmd.ExecuteReader(); // close conn after complete

                DataTable myDataTable = new DataTable();
                myDataTable.Load(reader);
                //myConnection.Close();

                string destinationConnectionString = "Data Source=####;Initial Catalog=DYOF_STAGING_BROC;User ID=;Password=;Connection Timeout=999";

                SqlConnection destination = new SqlConnection(destinationConnectionString);
                SqlBulkCopy bulkData;
                //destination.Open();

                Exception ex = null;
                try
                {
                    Console.WriteLine("step1");
                    bulkData = new SqlBulkCopy(destinationConnectionString, SqlBulkCopyOptions.FireTriggers);
                    bulkData.BulkCopyTimeout = 1;
                    bulkData.DestinationTableName = "Load_CTR_Sample_Account_Opening2";
                    bulkData.WriteToServer(myDataTable);
                    bulkData.Close();
                    Console.WriteLine("moved from here to there");
                    reader.Close();

                    //destination.Close();


                }
                catch (Exception e)
                {
                    ex = e;
                }

Answer

Read the data from Access into a DataTable:

string strConnect = @"Provider=Microsoft.ACE.OLEDB.12.0;data source=D:TempMyDB.accdb";

DataTable dt = new DataTable();
using (OleDbConnection con = new OleDbConnection(strConnect))
    {
    OleDbCommand cmd = new OleDbCommand("SELECT * FROM MyTable", con);
    con.Open();
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    da.Fill(dt);
    }

Then use SqlBulkCopy to update SQL:

string strConnect = @"Data Source=GRIFFPCSQLEXPRESS;Initial Catalog=Testing;Integrated Security=True";
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlBulkCopy bulk = new SqlBulkCopy(con))
        {
        bulk.DestinationTableName = "Test";
        bulk.WriteToServer(dt);
        }
    }

Of course, there is a much easier way to go straight from Access to SQL Server, using VBA, SQL , or other methods.

https://support.office.com/en-us/article/import-or-link-to-data-in-an-sql-server-database-a5a3b4eb-57b9-45a0-b732-77bc6089b84e

https://www.sqlshack.com/six-different-methods-to-copy-tables-between-databases-in-sql-server/