Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One instance of ADO.Connection for all procs
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One instance of ADO.Connection for all procs
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 In article , says... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One instance of ADO.Connection for all procs
Hi Todd
"Tod" wrote: 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 dont 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
One instance of ADO.Connection for all procs
TK wrote:
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
First Instance | Excel Discussion (Misc queries) | |||
How do I get rid of a 2nd instance (xls:2)? | Excel Discussion (Misc queries) | |||
how to make subs/procs can only be called from within the workbook they belong to? | Excel Programming | |||
Excel ADO connection opens read-only instance of opened Excel | Excel Programming |