Trapping MS Access Automation error -2147467259 in Windows Forms application

I have developed a Windows Forms application that uses an Access database to store information.  Using VS 2008.

While stress-testing the application (i.e. pressing buttons repeatedly to add/remove data items), I've come across error -2147467259, which tends to appear more on slower client machines, but is not very predictable.

I've had a look at this article http://support.microsoft.com/kb/286126 and although my symptoms are not exactly the same, it seems to be a similar issue.

I have tried using On Error to trap the error, check if it has the error code above and then Resume if it does, otherwise do other error processing.

This resolves the problem for the specific line of code that was producing the error before.  But now other parts of the code are producing the same error.

As I didn't want my code full of On Error statements, I thought I would use Try/Catch blocks to trap the error instead.  But this presents another problem, getting the code to resume execution if the error was the one mentioned above.

For instance

Try
  MyTableAdapter.Update()
Catch ex As OleDB.OleDBException
  If ex.ErrorCode = -2147467259 Then
    ' Do nothing
  Else
    ' Handle error
  End If
End Try

This works as it should because execution resumes after the Try block and has the same effect at Resume Next, right?

But if there are many lines within a function that could throw the exception then it's not so easy to go back to where the exception was thrown from.

So what is the best way to handle these types of error ?  Or maybe I shouldn't be handling them, maybe there is a fundamental problem somewhere?
LVL 1
devon-ladAsked:
Who is Participating?
 
CodeCruiserCommented:
The Reader fetches records one by one so it keeps the connection open and occupied. If you try to use the same connection again, you get an error. You have to call the  reader.close method and then you can reuse to connection without having to reopen it.
The connection and reader are never automatically closed unless they go out of scope. So try to use the Using construct to make sure you close the connection and reader once you have used it.
0
 
CodeCruiserCommented:
The link provided says that this error occurs when calling SetFocus to a custom menubar. Are you doing this in your code?
0
 
devon-ladAuthor Commented:
Actually no - so maybe I am completely on the wrong track.  However, I am connecting to Access and using the Automation features of .NET 3.5 - so I made a guess...perhaps wrongly...that this was a similar problem.

Since posting I've tried putting a delay timer on the button that is causing the problem.  The button is used to add new items to an equipment list.  Pressing it repeatedly and quickly will eventually give the error.  So I've set the button to disable itself until the timer ticks after a 500 milliseconds.  This appeared to resolve the problem.  However, when testing it on a slower machine, the errors appeared again until I set the delay to 1 second.

So it's not a very precise way of dealing with the error.

The exception is being thrown by a number of different lines in the code, all of which are calls to TableAdapter methods.  But as specified before, the errors only appear when the button is pressed very quickly and repeatedley.



0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
CodeCruiserCommented:
Instead of using the timer, disable the button at the start of the execution of code inside it and then re-enable it at the end of the execution. So

Button1.Click event

Button1.Enabled = False
'All the other code
Button1.Enabled = True
0
 
devon-ladAuthor Commented:
Yes, I tried this first, but it didn't have any effect.
0
 
CodeCruiserCommented:
Really? Strange. Is this button in Access or VB.NET? How is the button clicked?
0
 
devon-ladAuthor Commented:
I presume it is still doing some Access related processing after the button click event handler finishes.

It's part of a VB.NET Windows Foms application
0
 
CodeCruiserCommented:
Then the problem could be with the code being executed in the button. Can you post the full code?
0
 
devon-ladAuthor Commented:
Yes, you're right!  As I was posting the code I noticed something.

The button click handler calls another subroutine that uses an OleDbDataReader and a TableAdapter in a For Next loop.  Not quite sure why I opted for both methods, from the comments it looks like I was having problems getting the correct query through a TableAdapter method.  Anyway, the connection to the database via the OleDbDataReader object is not being closed before the TableAdaptor.Update method is being called.  Inserted a Connection.Close() line and now works fine.  I can keep the button pressed for 100s of repititions and no error.

I would have to admit, I'm not entirely sure why this is the case.  The OleDbDataReader was using different tables to the TableAdapter object.  And as the OleDbDataReader object and OleDbConnection were declared within the For Next loop, they should have been implicitly closed at the end of each loop.

Can you shed any light?

0
 
devon-ladAuthor Commented:
Ok, thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.