Ado Connection


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Ado Connection

  1. #1
    Sok Y. Kim Guest

    Ado Connection

    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.



  2. #2
    Aleksandr Guest

    Re: Ado Connection


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



  3. #3
    P.Montanino Guest

    Re: Ado Connection


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


  4. #4
    Sok Y. Kim Guest

    Re: Ado Connection

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




  5. #5
    Ted McNeal Guest

    Re: Ado Connection


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

    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center