Is it better to better to close the access DB after every operation of keep it open for later operations

I am working on a VB.NET project that grabs data from an Access DB file. All the code snipeets I have come across open the DB, do stuff and close it for each operation. I currently have the DB open for the 开发者_高级运维entire time the application is running and only close it when the application exits.

My question is: Is there a benefit to opening the connection to the DB file for each operation instead of keeping it open for the duration the application is running?

In many database systems it is good practice to keep connections open only when they are in use, since an open connection consumes resources in the database. It is also considered good practice for your code to have as little knowledge as possible about the concrete database in use (for instance by programming against interfaces such as IDbConnection rather than concrete types as OleDbConnection.

For this reason, it could be a good idea to follow the practice of keeping the connection open as little as possible regardless of whether it makes sense or not for the particular database that you use. It simply makes your code more portable, and it increases your chance of not getting it wrong, in case you in your next project happen to work against a system where keeping connections open is a bad thing to do.

So, your question should really be reversed: is there anything to gain by keeping the connection open?

There is no benefit with the Jet/ACE database engine. The cost of creating the LDB file (the record locking file) is very high. You could perhaps avoid that by opening the file exclusively (if it's a single user), but my sense is that opening exclusive is slower than opening multi-user.

The advice for opening and closing connections is based around an assumption of a database server being on the other end of the connection. If you consider how that works, the cost of opening and closing the connection is very slight, as the database deamon has the data files already open, and handles locking on the fly via structures in memory (and perhaps on disk -- I really don't know about how it's implemented in any particular server database) that already exist once the server is up and running.

With Jet/ACE, all users are contending for two files, the data file and the locking file, and setting that up is much more expensive than the incremental cost of creating a new connection to a server database.

Now, in situations where you're aiming for high concurrency with a Jet/ACE data store, there might have to be a trade-off among these factors, and you might get higher concurrency by being much more miserly with your connections. But I would say that if you're into that realm with Jet/ACE, you should probably be contemplating upsizing to a server-based back end in the first place, rather than wasting time on optimizing Jet/ACE for an environment it was not designed for.