OleDbConnection Access connection problem

[AndyLee100]

Hi all,

Hope someone can help.

I have a small vb.net 2005 application that sits on a customers server and checks an Access database every 5 seconds for exports and import files (if they exist) from exported files from an SAP system.

This all works well when the connection to the database is up and running. My problem is that the connection is regulary lost to the database and I get 'Disk or Network Error' trying to re-connect to the database.
Everytime I check for exports, I first try and connect to the database (this is where I get the error), then do all the work and the disconnect.

If I then close and re open the app it starts to all work again until the next time it breaks.

The program and database are on the same PC so there is no network to go through.

Any ideas ?

Many thanks

Andy

 

[AlexCuse]

Why not put the steps where you are using the database into a try/catch block, so that the error won't be a show stopper?

Then, when you are done in the DB, after about 5 seconds your app will be regularly checking again.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[AndyLee100]

Sorry, not sure what you mean.

The problem is not that the app crashes (it doesnt) but that it cannot connect to the database. However if I close the app and then re-open it will manage to connect and will carry on quite happily.

Sorry if I wasnt clear enough.

Cheers

Andy

 

[AlexCuse]

If I am reading correctly, your problem seems to be that if you encounter this error, you need to restart the app.

VB.net provides the try/catch block to allow your applications to handle errors more gracefully. Used properly, this will allow your app to keep running when it encounters the connection error.

http://www.homeandlearn.co.uk/NET/nets5p4.html

If I am misreading your problem, I apologize.

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[pmegan]

When this happens does the program continue to check every 5 seconds or does the loop stop looping?

 

[SgtJarrow]

I read your issue the same as Alex. I would do something like:

Try
con.Open
'your code to do whatever here
Catch
'Something to handle the error
Finally
'con.Close
End Try

The Try block begins to execute. If any error is encountered, the method immediately jumps to the Catch block and begins executing the code there. Either way, the Finally block is executed, in which you "gracefully" close your OleDb connection, so the next time you try to access it it does not encounter an Open state.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[AndyLee100]

All,

For some reason you have all jumped on this try...catch block ? I have the connect in a try..catch block which is why I am able to report the problem to the UI.

The connection is closed. The problem is trying to re-open it. Every 5 secs it opens the connection, checks for exports then closes it. For an unknown reason it cannot re-connect after a while and will only re-connect after I have closed and re-started the app.

It always reports 'Disk or Network Error' although the app and database are on the same PC.

 

[chrissie1]

Is this after a set number of opening and closing?

Most likely it is not closing.

ADO.NET does some fancy (or not so fancy) connection pooling for you so that could be the problem.

Try also disposing the connection or try to keep the connection open if there is only one user, perhaps not the best idea but in this case wel worth it. Opening and closing a connection is resource intensive.

BTW the reason it gives a network error is because the oledbconnection uses the TCP/IP stack (most likely)

Christiaan Baes
Belgium

"My old site" - Me

 

[AndyLee100]

chrissie,

It probably is after a set number of opening or closings, though I cannot prove this.

If the connection is open when I am going to reconnect I close it.

Shall I just keep the connection open for the lifetime of the app and not bother opening and closing ?

Thanks

Andy

 

[chrissie1]

In this case I would. Since it is used every 5 seconds. But this does not solve your problem.

Do you have some code for us to look at?

Christiaan Baes
Belgium

"My old site" - Me

 

[pmegan]

Could this have something to do with timing? If the database connection is global and your connecting to it in a thread kicked off when a 5 second timer ticks, you'll run into problems if an export runs longer than 5 seconds.

You can also try resetting the db connection in the catch block and see if it'll correct itself. This way you're only disposing of and reinitializing the connection when it chokes. It won't stop the underlying problem but it might keep the program from completely tanking. This isn't tested on an OldDb connection but should be similar to this SqlServer connection code I used on in a program that occasionally clogged up:

Catch ex As SqlClient.SqlException

   [green]' check if this is the exception you're looking for[/green]
   If ex.Number = [i]put error # here[/i] Then

      [green]' make sure the connection is actually closed[/green]
      If dbConn.State <> ConnectionState.Closed Then
	dbConn.Close()
      End If

      [green]' create a new connection instance[/green]
      dbConn.Dispose()
      dbConn = New SqlClient.SqlConnection([i]connection string[/i]

   End If
End Try

There are also some bugs in Access that throw this error, so this may not even be coming from your program.

http://support.microsoft.com/kb/814811

http://support.microsoft.com/kb/q251254/

http://www.google.com/search?num=50...r=all&q=+"Disk+or+Network+Error"+&btnG=Search

Hope this helps,
Pat