Ado Connection

[Sok Y. Kim]

Reading thru the discussions in this forum, my understanding is that it is
better to open and close the Ado connection at each call.
Is this also true if I am using Jet 4.0 driver?
What happens if I am opening 3 different recordsets in one module? Is this
mean I have to open 3 separate connections?
Does any one have a sample of Global Open connection and how moudules would
open 3 or more different recordsets using the global open connection?

tx.

[Aleksandr]

In my particular case, i don't close an reopen the connection once and again.
I do open the connection in the loading of my project and close it on app
close, since my app is data based, and every action in it is an action in
the database.
There's no problem with having a connection opened, what you must remember
is to open and close recordsets, in those cases when the same recordset object
is used once and again.
Aleksandr.

"Sok Y. Kim" <controlx@kornet.net> wrote:
>Reading thru the discussions in this forum, my understanding is that it
is
>better to open and close the Ado connection at each call.
>Is this also true if I am using Jet 4.0 driver?
>What happens if I am opening 3 different recordsets in one module? Is this
>mean I have to open 3 separate connections?
>Does any one have a sample of Global Open connection and how moudules would
>open 3 or more different recordsets using the global open connection?
>
>tx.
>
>

[P.Montanino]

"Aleksandr" <aca@mailops.com> wrote:
>
>In my particular case, i don't close an reopen the connection once and again.
> I do open the connection in the loading of my project and close it on
app
>close, since my app is data based, and every action in it is an action in
>the database.
>There's no problem with having a connection opened, what you must remember
>is to open and close recordsets, in those cases when the same recordset
object
>is used once and again.
>Aleksandr.
>
>"Sok Y. Kim" <controlx@kornet.net> wrote:
>>Reading thru the discussions in this forum, my understanding is that it
>is
>>better to open and close the Ado connection at each call.
>>Is this also true if I am using Jet 4.0 driver?
>>What happens if I am opening 3 different recordsets in one module? Is
this
>>mean I have to open 3 separate connections?
>>Does any one have a sample of Global Open connection and how moudules would
>>open 3 or more different recordsets using the global open connection?
>>
>>tx.
>>
>>
>
There was a lot of problem with leavin a connection opened.
Related to your question I think is better to open and close a connection
with a database every time You write and read a Database in this sequences.
For Exemple if you open two different connection to a database and with the
first you write in a Table and in the second you read the same table you
will not found the last add.
I solved this problem by using a connection object for every recodset opening
and closing it for each operation on recordset.
When I open a connection , write in database with a recordset.update and
then close the connection (a unique connection object) the following operation
of reading will found the add in the database.
What do you think of this?

[Sok Y. Kim]

Thanks for the inputs.
I tried both methods;opening and closing at each call and opening once at
beginning of application and closing at the end of application.
I am trying to understand the performance implication of opening and closing
connections at each call.
If I don't open and close at each call, I sometimes get a message saying
that recordset has been changed and I get an error when I try to .update.
Isn't opening and closing at each call an expensive resource?


P.Montanino <paolmon@libero.it> wrote in message
news:39fa08a9$1@news.devx.com...
>
> "Aleksandr" <aca@mailops.com> wrote:
> >
> >In my particular case, i don't close an reopen the connection once and
again.
> > I do open the connection in the loading of my project and close it on
> app
> >close, since my app is data based, and every action in it is an action in
> >the database.
> >There's no problem with having a connection opened, what you must
remember
> >is to open and close recordsets, in those cases when the same recordset
> object
> >is used once and again.
> >Aleksandr.
> >
> >"Sok Y. Kim" <controlx@kornet.net> wrote:
> >>Reading thru the discussions in this forum, my understanding is that it
> >is
> >>better to open and close the Ado connection at each call.
> >>Is this also true if I am using Jet 4.0 driver?
> >>What happens if I am opening 3 different recordsets in one module? Is
> this
> >>mean I have to open 3 separate connections?
> >>Does any one have a sample of Global Open connection and how moudules
would
> >>open 3 or more different recordsets using the global open connection?
> >>
> >>tx.
> >>
> >>
> >
> There was a lot of problem with leavin a connection opened.
> Related to your question I think is better to open and close a connection
> with a database every time You write and read a Database in this
sequences.
> For Exemple if you open two different connection to a database and with
the
> first you write in a Table and in the second you read the same table you
> will not found the last add.
> I solved this problem by using a connection object for every recodset
opening
> and closing it for each operation on recordset.
> When I open a connection , write in database with a recordset.update and
> then close the connection (a unique connection object) the following
operation
> of reading will found the add in the database.
> What do you think of this?
>

[Ted McNeal]

Sok,

For most ADO providers and ODBC drivers, opening and closing the connection
for each call is okay and may be a good idea. Both ADO and ODBC implement
connection pooling for most providers. This means that when you close a
connection, it actually remains open for a specified time limit and thus
is available for reuse. The connection can be reused if the connection parameters
are exactly the same as the connection parameters of the connection being
pooled. This is really a scalability point for MTS/COM+ development.

In ADO, there is a property of the connection object whereby you can interrogate
if the instantiated provider is pooling connections. The property is in
the form of a bit flag in the "OLE DB Services' property of the connection
object. You can also set this property. An enum of all of the bit flags
for this property exists in ADO.

In ODBC you can get/set the connection pooling flag and set a 'time to release'
in the ODBC management applet.

The connection pooling capabilities of ADO and ODBC are most significant
for MTS/COM+ objects. This is because the release of a connection can be
used by another object context before the actual connection is dropped (resource
pooling). This is one great feature about developing applications that target
COM+ services.

Using and understanding the Jet provider can be a little tricky. This is
because Jet is a file server database engine. The Jet provider does not
support dynamic cursors. If you open a client side cursor in Jet, then no
matter which cursor type you select you will always return a static cursor.
A static cursor library cannot see changes made by other users. Opening
a server side cursor will allow you to specify a static, keyset, or forward
only cursor. Your error message may be occurring because you are not really
using a keyset cursor and hence cannot see changes by other users. A keyset
type cursor is the default cursor type for DAO accessing a Jet database.
In DAO it is called dynaset. So, try using a server side keyset cursor
for recordset operations.

A simple way to determine if Jet is pooling your connections is to use ldbview
to watch the connection activity on your database file. Microsoft created
this tool. Hence, it should be available from the Microsoft knowledge base
or mdsn site for Jet/Access developers. I would suggest checking if the
Jet provider is set or capable of pooling connections. If not, keep the
connection open yourself.

Another important issue is that three open resultsets from a single connection
object is one connection. Yet, three resultsets from separate connection
objects is three separate connections.

I think that it is important to note that Jet only supports 255 concurrent
connections. Each time you connect to Jet the database header is read and
collections of the database must be populated. This is a very costly operation.
This is the common mistake of Access developers using Currentdb() as oppose
to using DBEngine(0)(0) to establish a reference to the current database.
This is an issue because the engine is on the client and it is roughly equivalent
to starting the database server each time you establish a connection. There
may be some optimizations in the Jet Provider to use the existing information
from currently established connection, but I doubt it. Thus, try to use
a single connection object for all synchronic operations.

Connection pooling is not connection sharing. I mean that a connection cannot
be picked up by another thread/process until it is released to the pool.
In your case, because a connection can be pooled it still means that if
you instantiate 10 connections that you will still have 10 connections.

Ted McNeal



"Sok Y. Kim" <controlx@kornet.net> wrote:
>Thanks for the inputs.
>I tried both methods;opening and closing at each call and opening once at
>beginning of application and closing at the end of application.
>I am trying to understand the performance implication of opening and closing
>connections at each call.
>If I don't open and close at each call, I sometimes get a message saying
>that recordset has been changed and I get an error when I try to .update.
>Isn't opening and closing at each call an expensive resource?
>
>
>P.Montanino <paolmon@libero.it> wrote in message
>news:39fa08a9$1@news.devx.com...
>>
>> "Aleksandr" <aca@mailops.com> wrote:
>> >
>> >In my particular case, i don't close an reopen the connection once and
>again.
>> > I do open the connection in the loading of my project and close it
on
>> app
>> >close, since my app is data based, and every action in it is an action
in
>> >the database.
>> >There's no problem with having a connection opened, what you must
>remember
>> >is to open and close recordsets, in those cases when the same recordset
>> object
>> >is used once and again.
>> >Aleksandr.
>> >
>> >"Sok Y. Kim" <controlx@kornet.net> wrote:
>> >>Reading thru the discussions in this forum, my understanding is that
it
>> >is
>> >>better to open and close the Ado connection at each call.
>> >>Is this also true if I am using Jet 4.0 driver?
>> >>What happens if I am opening 3 different recordsets in one module?
Is
>> this
>> >>mean I have to open 3 separate connections?
>> >>Does any one have a sample of Global Open connection and how moudules
>would
>> >>open 3 or more different recordsets using the global open connection?
>> >>
>> >>tx.
>> >>
>> >>
>> >
>> There was a lot of problem with leavin a connection opened.
>> Related to your question I think is better to open and close a connection
>> with a database every time You write and read a Database in this
>sequences.
>> For Exemple if you open two different connection to a database and with
>the
>> first you write in a Table and in the second you read the same table you
>> will not found the last add.
>> I solved this problem by using a connection object for every recodset
>opening
>> and closing it for each operation on recordset.
>> When I open a connection , write in database with a recordset.update and
>> then close the connection (a unique connection object) the following
>operation
>> of reading will found the add in the database.
>> What do you think of this?
>>
>
>