Is it really wise to close connections immediately with Jet/ADO?

Question

I've done extensive research on this topic and intensive discussions with my co-workers.

The general consensus seems to be, that a database connection should be opend when needed and closed immediately. Any caching/pooling of connections should be done by the database driver or some other layer, but not by the application itself.

But I have my doubts when it comes to Jet/ADO. Jet and/or ADO use read caches and lazy writes which can lead to "unsynchronized" connections. Of course I could just re-synchronize the connections every time I use them using JRO, but that seems like quite a hassle and a possible performance drain.

Should I go with the "best practice" of closing each connection and re-sync every new connection or should I just go with a global connection object because of the Jet/ADO quirks?

Answer 1

Most of the modern DBMS provide ConnectionPool to mange the connections. however when you use connection.close(); you don't really close the connection it just return it back to the Pool and when you write connection.open(); in fact it don't create a new connection you are just take a prepared connection in ConnectionPool and use it.

So Open the connection and close it every time you want to pass the query to the DBMS is the best way and i don't cause problem in performance, In addition to you can't use the same connection with several data readers SQLDataReader.

for that don't use global connection especially when developing websites because when there is heavy traffic on your site it will throw exceptions randomly when two users are parsing your pages causing 2 data readers trying to use the same connection.

Answer 2

I think it's best to use one global connection for VB6 desktop applications. Ideally the database driver would manage connection pooling transparently, but when you're using Jet with ADO or DAO in VB6 it doesn't: the OLE DB Jet Provider doesn't have connection pooling. I believe the .NET providers do.

I've assumed your application is a desktop app - you haven't actually specified. I've no experience of IIS applications or COM+ so I don't know what's best there. Probably not to use Jet Access! It's fine for desktop apps though.

Answer 3

The last two large VB6 + ADO + Access database engine apps I worked on (two different teams/employers) used the same approach:

• Use a client side cursor
• Keep the ADODB.Connection open
• Immediately disconnect each ADODB.Recordset using Set rs.ActiveConnection = Nothing

Answer 4

One of the issues with Jet/ACE is that the creation of the locking file (*.ldb) can impose a significant performance penalty. Also, if the LDB already exists (because another user is connected), setting up the locking can take significant time.

Thus, you want to minimize the number of times your app connects, because you could be wasting time on constantly recreating and reconnecting to the LDB file.

In Access apps, it's common practice among many programmers to maintain a persistent connection.

The usual recommendation is to open a table in a hidden form or open a recordset on a table. I prefer initializing a database variable pointing to the back end. Tony's objections in the cited article seem pretty insignificant to me -- it's simply not that hard to parse the connect string, e.g., Mid(CurrentDB.TableDefs("MyLinkedTable").Connect, 11).

In a non-Access context, you have the same problem, because this is a Jet/ACE database engine issue. The particular methods of maintaining an open connection would be different, of course, but the point is that keeping it open is going to mean less contention by the db engine for the locking file.