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?
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.