How to remove database connection

[Mr.M]

Hi Dw.

I have a timer that in every 600 interval access the database and the database I'm using is MS Access database. The problem I have is that with this being said, the MS Access database has a limitation of 64 connections on each thread so this 600 interval does reaches the limitation, but I don't want that to happen.

This is what I have and I think its where I should be removing the connection. Code:

 Public conn As OleDbConnection
 Public Sub ConnDB()
 Try
 conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="c:\test.mdb;Jet OLEDB.Database Password = 1234;")
 conn.Open()
 Catch ex As Exception
 End Try

Now as you can see its says conn = New OleDbConnection which means when ever the timer fires a ConnDb() then a new connection is made, but keep in mind that doing this adds numbers of current connected connections and the limit is 64 so when ever the timer fires the new connection is created.

Now what I want is that when the accessing of the database is complete as per that command that was fired by the timer, I need to remove the connection so that one form/thread won't reach the limit and that I want to remove unused connections to prevent this.

Because if I remove these connections then that will means I can have exactly 64 threads running accessing the database.

I thinks this is clear of what I want to achieve, if there is any confusion you are more then welcome to point out where you need a clarification and I will do so.

[hericles]

Simply dispose of the connection when you're done with it.

conn.Close()
conn.Dispose()

That's just good practise anyway for db connections.

[rproffitt]

This sounds like a carry over from your other discussion thread. Even if we use MySQL you don't hold the connection open for longer than you need it. Yes you may incur some overhead from this but if you want to support hundreds of clients you have to keep it cleaned up.

Another thought is that you are polling rather than updating on change. This creates a lot of needless work all around. Yes, it's fine for your first efforts but later you need to change your designs away from polling.