question

taiseiishiyama-8785 avatar image
0 Votes"
taiseiishiyama-8785 asked ·

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

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);
                 }
             });
         }
dotnet-csharpoffice-access-dev
· 14
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Does it crash silently?

0 Votes 0 ·

Thank you for your comment.
I'm not sure if this answers your question, but the application logs are still there.
I don't know if this will help, but I've attached the detailed xml below.68967-20210217-applogs.png


0 Votes 0 ·
Viorel-1 avatar image Viorel-1 taiseiishiyama-8785 ·

Maybe you can add a try-catch statement (inside the code of Task.Run) and investigate the details and stack-trace of exception (including inner exceptions). Perhaps this will give some more information. (Assuming that this error can be intercepted).

0 Votes 0 ·
Show more comments

Only one simple question: is this a asp.net application, or a desktop one?

there is a issue with connection pooling in asp.net - EVEN WHEN using a "USING --->END USING" block, after 64 opens, it WILL crash. You HAVE to dispose of the object to prevent this.

However, to my knowledge, this only occurs if you using a asp.net site and the reason/cause is a issue in connection pooling.

If this is a deskop? Then you could try a "using block", but right before the last line of the using block, do a dispose on your command object or connection object.

This could hurt performance - and some decision here as to if the advantage flips to keeping (forcing) the connection to stay open vs that of performance.

However, right now? This being a asp.net site is 100% relevant here. You MUST use dispose as a "using block" simply does not suffice.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada




0 Votes 0 ·

Thank you for your comment.
The application I'm using is a desktop application.

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. Do you have any idea what could be going on?

0 Votes 0 ·

Ok, no there not enough information as of yet to figure out this issue.

But like any troubleshooting, we have to eliminate the obvious first. it was CRITICAL to know if this was a asp.net based application since the connection pooling issue would be something to zero in on. Since this is not a web based system, then we can eliminate my question, and thus eliminate connection pooling by a web site as your issue.

Ok, no connection pooling by a web site - we scratch that!!

Next up:
IIs the software (.net code) running on the same computer as the database (accDB) file, or is a network involved?
Do more then one client workstation open this shared back end, or is there ONLY one software instance and the access database is on the SAME computer.

So is a network involved, or is this simply a desktop program, and the use of he database is on the same computer?





0 Votes 0 ·
Show more comments
vb2ae avatar image
0 Votes"
vb2ae answered ·

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);

}

· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your comment.
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…

0 Votes 0 ·
vb2ae avatar image vb2ae taiseiishiyama-8785 ·

what is the value of i when the app crashes? What does the stored procedure you are calling do?

0 Votes 0 ·

Thanks again for your comments.
I did not record the exact value of i when the crash occurred, but I think it was roughly between 50000~100000.
I will try the experiment again and check.

The query I am running is the one below.

 private readonly string _query = "select * from tableSample;";


0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered ·

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
             {
                    
             }
         }
     }
 }



· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your comment.
Am I correct in understanding that you are advising that I should check for exceptions?
In this case, I only added Try-catch like the code above to check it easily, but is there anything missing?

0 Votes 0 ·
DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered ·

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.


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your comment!
I am sorry, but I did not understand the intent of your comment.
Is there something about cutting out Task to method that works better for this issue?
I tried to run the code you gave me, but the crash still occurred...

0 Votes 0 ·