In Access 2016, for high frequency access, creating an instance of OLEDB connection for each access may cause the client application to crash

Question

Hello everyone.
I would like to ask a question about Access 2016.
I am using Access 2016 from an application created in C# using an OLEDB provider.
When I create a connection instance for every access, the application crashes. The frequency is about once every half day.
The code is as follows

private void button1_Click(object sender, EventArgs e)
{
Task.Run(async () =>
{
var i = 0;
while (true)
{
using var connection = new OleDbConnection(_connectionString);
await connection.OpenAsync();
using var commend = new OleDbCommand(_query, connection);
_ = await commend.ExecuteNonQueryAsync();
Invoke((MethodInvoker)(() => textBox1.Text = (++i).ToString()));
await Task.Delay(100);
}
});
}

I have also found that this problem can be remedied by making sure to reuse the connection.

private void button2_Click(object sender, EventArgs e)
{
Task.Run(async () =>
{
var i = 0;
while (true)
{
if (_connection == default(OleDbConnection))
{
_connection ?? = new OleDbConnection(_connectionString2);
await _connection.OpenAsync();
}
using var commend = new OleDbCommand(_query, _connection);
_ = await commend.ExecuteNonQueryAsync();
Invoke((MethodInvoker)(() => textBox1.Text = (++i).ToString()));
await Task.Delay(100);
}
});
}

I would like to know for the sake of later learning, is the above usage not recommended in Access 2016?
Thank you very much.

Added on 2021-02-17

I'm sorry that there was a problem with my code.
I tried modifying it to use the Using block, but that didn't solve the problem and caused the crash.

private void button1_Click(object sender, EventArgs e)
            {
                Task.Run(async () =>
                {
                    var i = 0;
                    while (true)
                    {
                        using (var connection = new OleDbConnection(_connectionString))
                        {
                            await connection.OpenAsync();
                            using (var commend = new OleDbCommand(_query, connection))
                            {
                                _ = await commend.ExecuteNonQueryAsync();
                                Invoke((MethodInvoker)(() => textBox1.Text = (++i).ToString()));
                            }
                        }

                        await Task.Delay(100);
                    }
                });
            }

Added on 2021-02-18
Added a try catch clause to check for exceptions. However, I could not catch the exception and it crashed.

[HandleProcessCorruptedStateExceptions()]
        private void button1_Click(object sender, EventArgs e)
        {
            Task.Run(async () =>
            {
                var i = 0;
                while (true)
                {
                    try
                    {
                        using (var connection = new OleDbConnection(_connectionString))
                        {
                            await connection.OpenAsync();
                            using (var commend = new OleDbCommand(_query, connection))
                            {
                                _ = await commend.ExecuteNonQueryAsync();
                                Invoke((MethodInvoker)(() => textBox1.Text = (++i).ToString()));
                            }
                        }
                    }
                    catch
                    {
                        Debug.WriteLine("error");
                    }

                    await Task.Delay(100);
                }
            });
        }

Answer 1

I think you are running out of available connections. I would change the code to this. I think the using block will make sure the connection is closed when you are done with it.

{
     using( var connection = new OleDbConnection(_connectionString))   
     {
            await connection.OpenAsync();
            using( var commend = new OleDbCommand(query, connection))
            {
                   var data = await commend.ExecuteNonQueryAsync();
                    Invoke((MethodInvoker)(() => textBox1.Text = (++i).ToString()));
             }
     }
    await Task.Delay(100);

}

Answer 2

Here is a start of a code flow to consider which uses events for reporting success and if an exception is thrown. The two events in the form need actions e.g. show in a MessageBox on finished if successful or not while the exception event indicates the exception by reading the exception message.

Data class

using System;
using System.Data.OleDb;
using System.Threading.Tasks;

namespace OleDbConnectionUpdateData
{
    public class DataOperations
    {
        private static string _connectionString = "TODO";

        public delegate void OnFinish();
        public static event OnFinish OnFinishedEvent;

        public delegate void OnException(Exception sender);
        public static event OnException OnExceptionEvent;

        public static async Task<bool> PerformUpdateTask()
        {

            return await Task.Run(async () =>
            {
                using (var cn = new OleDbConnection(_connectionString))
                {

                    using (var cmd = new OleDbCommand() { Connection = cn })
                    {

                        cmd.CommandText = "TODO";

                        try
                        {
                            await cn.OpenAsync();
                            var result = await cmd.ExecuteNonQueryAsync();
                            OnFinishedEvent?.Invoke();
                            return result == 1;
                        }
                        catch (Exception ex)
                        {
                            OnExceptionEvent?.Invoke(ex);
                            return false;
                        }
                    }
                }

            });
        }

    }
}

Form code

Note I don't pass anything to the update method, that you can handle.

using System;
using System.Windows.Forms;

namespace OleDbConnectionUpdateData
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            DataOperations.OnExceptionEvent += OnExceptionEvent;
            DataOperations.OnFinishedEvent += OnFinishedEvent;
        }

        private void OnFinishedEvent()
        {

        }

        private void OnExceptionEvent(Exception sender)
        {

        }

        private async void button1_Click(object sender, EventArgs e)
        {
            var result = await DataOperations.PerformUpdateTask();
            if (result)
            {

            }
            else
            {

            }
        }
    }
}

Answer 3

Hi taiseiishiyama-8785,
You can try to create an async Task method.
Please refer to the following code:

private void button1_Click(object sender, EventArgs e)  
{  
    test();  
}  
async Task test()   
{  
    var i = 0;  
    while (true)  
    {  
        using (var connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Desktop\test.accdb"))  
        {  
            connection.Open();  
            using (var commend = new OleDbCommand("select Name form Table1", connection))  
            {  
                var data = commend.ExecuteNonQueryAsync();  
                Invoke((MethodInvoker)(() => textBox1.Text = (++i).ToString()));  
            }  
        }  
        await Task.Delay(100);  
  
    }  
}  

Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.