0

I am currently working on a C# project to export a datatable from C# to an Access accdb file.

For the exportation function, I am using this function which comes from another post: Writing large number of records (bulk insert) to Access in .NET/C#

public static void InsertDataIntoAccessTable_Version3(DataTable dtOutData, String DBPath, String TableNm)
        {
            DAO.DBEngine dbEngine = new DAO.DBEngine();
            Boolean CheckFl = false;
            DateTime start = DateTime.Now;

            try
            {
                DAO.Database db = dbEngine.OpenDatabase(DBPath);
                DAO.Recordset AccesssRecordset = db.OpenRecordset(TableNm);
                DAO.Field[] AccesssFields = new DAO.Field[dtOutData.Columns.Count];

                //Loop on each row of dtOutData
                for (Int32 rowCounter = 0; rowCounter < dtOutData.Rows.Count; rowCounter++)
                {
                    AccesssRecordset.AddNew();

                    Console.WriteLine(rowCounter);
                    //Loop on column
                    for (Int32 colCounter = 0; colCounter < dtOutData.Columns.Count; colCounter++)
                    {
                        // for the first time... setup the field name.
                        if (!CheckFl)
                            AccesssFields[colCounter] = AccesssRecordset.Fields[dtOutData.Columns[colCounter].ColumnName];
                        AccesssFields[colCounter].Value = dtOutData.Rows[rowCounter][colCounter];
                    }

                    AccesssRecordset.Update();
                    CheckFl = true;
                }

                AccesssRecordset.Close();
                db.Close();

                double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
                Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(dbEngine);
                dbEngine = null;
            }
        }

According to the another post, it exported 120,000 Rows - 20 columns in 4 seconds. However, I cannot achieve such performance in my case where the datatable contains 1 Million rows and 29 columns. According to my estimation, it takes more then 20 minutes to finish.

On the other hand, I also came across another method using adatper but I cannot implement it yet.

I would like to know whether you have other solutions, suggestions, or advice to perform a much faster exportation from C# datatable to Access table.

For the technical environment, I am using Visual Studio 2017 32bit and Access 365.

Thank you in advance.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
RuanRuan
  • 22
  • 3
  • Access is a bit allergic when you increase the data amount past certain levels. Have you tried less data? There is always the other way around, a VBA Code that imports the data from e.g. an intermediate file. – Stejin Jun 01 '20 at 18:08
  • @Stejin: For your first question, I tested with small samples (< 100 rows) and it worked fine but I did not experiment it with 500k rows yet. I also try to avoid using VBA either in Access or Excel in this case but I will also try Access vba later. – RuanRuan Jun 01 '20 at 19:00
  • Directly using the DAO object as you have is certainly the BEST approach. How are you loading up that single source table with 1 million rows? In other words, where and how are you loading up that large table, how long does it take to load up, and how long does a simple for/each loop on that large table? Is there any network between you and the output accDB file? You should get around at least 100,000 rows per second here. This assumes no network between you and the accDB /mdb file. Also, check if indexing exists for the target table (no indexs will run much faster). – Albert D. Kallal Jun 03 '20 at 23:13

0 Answers0