Member Avatar for kentuckyjoe

I have a question about best practices as far as opening and closing the database connetion in my app. Currently I am opening and closing each time the database is accessed with a query or update. Should I be doing this? It’s a lot of opens and closes. Or maybe I should just open when the user signs on and close when he/she signs off. Or some other idea? Do you have any ideas or suggestions for me on this? I'm using VB.net to access an Access database.

  • 2 Contributors
  • 4 Replies
  • 177 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by Reverend Jim
Member Avatar for Reverend Jim

It depends on where the database is and how heavily it is being used. If the database is on a server and it is heavily used by a large number of people/applications concurrently then best practice is to keep your connection closed until you need it. Then open it, use it, and close it again. The reason is that there are a limited number of connections available so you want to tie one up as little as possible. If your processing is in a loop and the loop will be executed without interruption then open the connection, process all records in the loop, then close it once the loop has ended.

Member Avatar for kentuckyjoe

Rev Jim, thanks much. I feel better about the way I'm doing it now. Although I don't currently have a large group of simultaneous users it is my hope to someday have that. The opening and closing doesn’t seem to be any kind of performance drag so I will continue to do it as I have been. I’ve been wondering about this for a long time; I should have asked a long time ago. Thanks RJ.

Member Avatar for kentuckyjoe

I consider this question solved but am not sure I handled the indication of that correctly. I hope so.

Member Avatar for Reverend Jim

If you think you may have heavier use in the future then I strongly suggest you change from Access to a SQL database. Access is suitable for small, low-load databases but it does not scale well. I had to maintain one appliciation based on an Access database and I ended up having to take it offline every week to do a repair/rebuild because it kept on breaking. MS SQL is also much easier to administer.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.