One instance of ADO.Connection for all procs

[Tod]

I have several procedures that use ADO to connect to a
database and retrieve Recordsets. In each of these
procedures I open a Connection, use some SQL to get the
Recordset, then close the Connection.

Since all of the procedures are using the same Connection
to the same database, it seems I should be able to open
the Connection once, have all my procedures use that
Connection, then close and destroy the object when I'm
done.

My initial idea as to have a class module that I use to
create that instance. But.... that's where I reach the
cul de sac on my knowledge of this topic.

Any help?
tod

 

[Tushar Mehta]

Here's how I would do it; in fact, I recently finished a project that
leverages this technique.

Sub Proc1(aConn As ADODB.Connection)
Dim aRS As ADODB.Recordset
aRS.Open "{appropriate argument}", aConn
'...
End Sub
Sub Proc2(aConn As ADODB.Connection)
Dim aRS As ADODB.Recordset
aRS.Open "{appropriate argument}", aConn
'...
End Sub
Sub Main()
Dim aConn As ADODB.Connection
aConn.Open 'appropriate arguments
Proc1 aConn
Proc2 aConn
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Hi Todd

I have several procedures that use ADO to connect to a
database and retrieve Recordsets. In each of these
procedures I open a Connection, use some SQL to get the
Recordset, then close the Connection.

Since all of the procedures are using the same Connection
to the same database, it seems I should be able to open
the Connection once, have all my procedures use that
Connection, then close and destroy the object when I'm
done.

My initial idea as to have a class module that I use to
create that instance. But.... that's where I reach the
cul de sac on my knowledge of this topic.

Any help?
tod

Obviously you can keep a connection open as long as you
like, you just don’t close it but the recommended
way (by those that recommended such things) is to open
and close the connection as needed. The main reason among
others is to prevent memory leeks.
This is not to say you cannot issue several SQL statements in the
same procedure and then close the connection

Good Luck
TK

 

[onedaywhen]

open
and close the connection as needed. The main reason among
others is to prevent memory leeks

I've not heard this. Any details, please?

I think the connection strategy should depend on how often you are
hitting the database.

Disconnected is the model favoured for ASP e.g. fetch some data,
disconnect and show it to the user. At this point you don't know if you
will ever get postback e.g. the user may simply close their browser, so
the session is eventually timed out.

With a Window's application where the database is to store application
data, I tend to keep one connection open for the duration; I normally
know when the application is closing and so can disconnect cleanly. In
a multiuser environment disconnecting at strategic times may be
necessary but it can a pain to manage. I've also found that if a single
application is hitting the database frequently (i.e. many times per
second) then disconnecting each time can cause problems with the server
becoming 'unavailable'.

There are specific issues with MS Jet/Access. Jet can only safely
support a limited number of concurrent connections, say a dozen, so
releasing connections frequently is a good idea. However, due to jet's
read cache and lazy writes model, changes made by one connection may
not be immediately visible to another connection, even when opened by
the same client. This can be managed in an application (see the info in
see http://support.microsoft.com/?kbid=240317) but it's a lot simpler
if a single connection can kept open.

Jamie.

--