"The connection object is just a pointer to a database."

Strictly speaking, I don't think this is quite true.  A connection object
actually provides the link between a data *consumer* and a data *provider*,
and not the database itself.  Thus, in a typical ADO environment, the ADO
connection object will provide the link between, say, an OLEDB provider and
a command object or recordset object (consumers).

The connection object communicates with the provider (e.g. OLEDB, ODBC, JET,
ORACLE, etc.) which in turn communicates with its database or other data
store.  Indeed, providers exist that don't use a database at all.  Thus, the
connection object provides a level of abstraction which exposes a
(relatively) common behavior and interface to all the disparate providers
available.

In a VB environment, we rarely deal directly with a provider, although the
properties collection of the connection object does provide access to
provider specific property values which may influence the provider's
behavior.

For the poster with the memory leak, I'd look to the leak being in the
provider he is using and not to the ADO connection object.

As to the question of whether it's "better" to open a connection and hold
it, or open it, use it then release it,  I think the question is sort of
like "How fast can you drive your car?".  The answer is "It depends".  There
is no question that an open connection consumes server resources.  Your
database licensing scheme may also restrict the number of concurrent
connections allowed, so holding a connection ultimately will not scale.  At
some point, you'll run out of licenses or memory or other server resources,
so too many connections will at some point simply not work.  On the other
hand, creating and destroying a connection object does take time, pooling
notwithstanding, and generally speaking this extra effort adds complexity to
the application, so individual application performance can suffer if it is
constantly creating and releasing connections.

The question then, is how many connections is too many?  Many applications I
have written have been for a small number of users, perhaps up to a dozen or
so, and I think it is quite acceptable in these cases to use a global
connection object and hold it open for the life of the application.  If, on
the other hand, your application has any potential at all to be scaled
upwards to many users, then you are better off using the "grab it, use it,
release it" approach.  That having been said, not every application will
ultimately be implemented on the Internet with concurrent user counts
numbering in the thousands, so scaling concerns should be taken with a grain
of salt (IMO)...
--
Jeff Mason			Custom Apps, Inc.
Jeff@c...