0

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
12
Views
3 Years
Discussion Span
Last Post by Reverend Jim
0

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.

0

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.

0

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

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.