sharing ADO connection across multiple VB EXEs


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: sharing ADO connection across multiple VB EXEs

Hybrid View

  1. #1
    Chris Hylton Guest

    sharing ADO connection across multiple VB EXEs


    Has anybody found a way to share an ADO connection across multiple EXEs (all
    apps and components done with VB6). I want to be able to have an exe or
    activex exe/document create an ADO connection, then be able to get to that
    connection from inside other EXEs that are started AFTER the first EXE establishes
    the connection. The goal is to have a single connection to the database
    per client machine for our application.

    I know that you can't GetObject on a component created in VB.

    I found the RotHook/RotClock example here on DEVX and I'm reading through
    that now, but it's getting pretty deep and I'm looking for any other solutions
    (other than building a C++ component) to solve this issue.

    The final solution may end up being to leave this alone until we move to
    .NET and then build this application the right way, but for now, we are stuck
    w/ the existing design from 5+ years ago that we have to maintain for the
    time being.

    Thanks,
    Chris

  2. #2
    michael Guest

    Re: sharing ADO connection across multiple VB EXEs


    Here is a link. I could not find a link to the pooling toolkit at microsoft.
    However, I do have the toolkit burried in an email if you would like for
    me to send it to your email. Let me know.

    http://msdn.microsoft.com/library/de...l/pooling2.asp

    Hope this may help.
    Michael

    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >Has anybody found a way to share an ADO connection across multiple EXEs

    (all
    >apps and components done with VB6). I want to be able to have an exe or
    >activex exe/document create an ADO connection, then be able to get to that
    >connection from inside other EXEs that are started AFTER the first EXE establishes
    >the connection. The goal is to have a single connection to the database
    >per client machine for our application.
    >
    >I know that you can't GetObject on a component created in VB.
    >
    >I found the RotHook/RotClock example here on DEVX and I'm reading through
    >that now, but it's getting pretty deep and I'm looking for any other solutions
    >(other than building a C++ component) to solve this issue.
    >
    >The final solution may end up being to leave this alone until we move to
    >.NET and then build this application the right way, but for now, we are

    stuck
    >w/ the existing design from 5+ years ago that we have to maintain for the
    >time being.
    >
    >Thanks,
    >Chris



  3. #3
    Paul Clement Guest

    Re: sharing ADO connection across multiple VB EXEs

    On 6 Jun 2002 11:51:19 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:


    Has anybody found a way to share an ADO connection across multiple EXEs (all
    apps and components done with VB6). I want to be able to have an exe or
    activex exe/document create an ADO connection, then be able to get to that
    connection from inside other EXEs that are started AFTER the first EXE establishes
    the connection. The goal is to have a single connection to the database
    per client machine for our application.

    I know that you can't GetObject on a component created in VB.

    I found the RotHook/RotClock example here on DEVX and I'm reading through
    that now, but it's getting pretty deep and I'm looking for any other solutions
    (other than building a C++ component) to solve this issue.

    The final solution may end up being to leave this alone until we move to
    NET and then build this application the right way, but for now, we are stuck
    w/ the existing design from 5+ years ago that we have to maintain for the
    time being.

    The ODBC and OLEDB managers already handle connection pooling if you're using a common connection
    string. I wouldn't recommend sharing a single connection amongst your components.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  4. #4
    Chris Hylton Guest

    Re: sharing ADO connection across multiple VB EXEs


    Thanks Paul and Michael,

    Still not sure I understand what's going on underneath the hood here...I've
    read the article that Michael posted above as well as everything else I could
    find in MSDN on ado connection pooling.

    My problem is this (more specifically)...the way my current clients application
    is structured it that there are multiple EXEs (standard vb programs) that
    comprise the overall application in logical blocks of business functionality.
    Each EXE is 'fired' from a common toolbar from a standard shell/shellexecute
    statement. The design was implemented years ago by the powers that be at
    the time. The primary reason was the relative size of the application, there
    are TONS of little apps that comprise the overall app, if they were one MDI
    interface, the EXE would be HUGE.

    So...what happens is that when each EXE if opened by the toolbar (which itself
    establishes a connection to the database, each EXE then establishes a connection
    to the database. Every app uses the same user credentials, they are passed
    via the command-line in the shell execute statement (again, not the greatest
    design, but that's the way it is).

    Anyway...once you have mulitple apps open, if you go to the Oracle session
    list, that user is connected to the database multiple times, one for each
    app that's open on their desttop.

    That's what I'd like to get around...figure out a way to open a single connection
    in ADO and have that connection used by all the applications...simple concept,
    but difficult to implement in VB (the getobject issue) w/o using VC++ or
    building a MTS solution.

    Any ideas ?

    Am I way off-base in my expectations here ?

    Chris

    Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >On 6 Jun 2002 11:51:19 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >
    > Has anybody found a way to share an ADO connection across multiple EXEs

    (all
    > apps and components done with VB6). I want to be able to have an exe

    or
    > activex exe/document create an ADO connection, then be able to get to

    that
    > connection from inside other EXEs that are started AFTER the first EXE

    establishes
    > the connection. The goal is to have a single connection to the database
    > per client machine for our application.
    >
    > I know that you can't GetObject on a component created in VB.
    >
    > I found the RotHook/RotClock example here on DEVX and I'm reading through
    > that now, but it's getting pretty deep and I'm looking for any other solutions
    > (other than building a C++ component) to solve this issue.
    >
    > The final solution may end up being to leave this alone until we move

    to
    > NET and then build this application the right way, but for now, we are

    stuck
    > w/ the existing design from 5+ years ago that we have to maintain for

    the
    > time being.
    >
    >The ODBC and OLEDB managers already handle connection pooling if you're

    using a common
    >connection
    >string. I wouldn't recommend sharing a single connection amongst your components.
    >
    >
    >Paul ~~~ pclement@ameritech.net
    >Microsoft MVP (Visual Basic)



  5. #5
    michael Guest

    Re: sharing ADO connection across multiple VB EXEs


    Does the connection to the database stay active the entire time the these
    mini-exe's are loaded? If yes and they only do a few seconds of data access/manipulation,
    then disconnect the connection with in the exe. You can alway reconnect it
    if needed since all of the information is already contained with in the exe
    to connect it the first time.
    I have done a few 2 tier apps using disconnected recordsets with 30+ users.
    And very rarely do I see more than 2 or 3 of users when doing a sp_who.

    Michael

    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >Thanks Paul and Michael,
    >
    >Still not sure I understand what's going on underneath the hood here...I've
    >read the article that Michael posted above as well as everything else I

    could
    >find in MSDN on ado connection pooling.
    >
    >My problem is this (more specifically)...the way my current clients application
    >is structured it that there are multiple EXEs (standard vb programs) that
    >comprise the overall application in logical blocks of business functionality.
    > Each EXE is 'fired' from a common toolbar from a standard shell/shellexecute
    >statement. The design was implemented years ago by the powers that be at
    >the time. The primary reason was the relative size of the application,

    there
    >are TONS of little apps that comprise the overall app, if they were one

    MDI
    >interface, the EXE would be HUGE.
    >
    >So...what happens is that when each EXE if opened by the toolbar (which

    itself
    >establishes a connection to the database, each EXE then establishes a connection
    >to the database. Every app uses the same user credentials, they are passed
    >via the command-line in the shell execute statement (again, not the greatest
    >design, but that's the way it is).
    >
    >Anyway...once you have mulitple apps open, if you go to the Oracle session
    >list, that user is connected to the database multiple times, one for each
    >app that's open on their desttop.
    >
    >That's what I'd like to get around...figure out a way to open a single connection
    >in ADO and have that connection used by all the applications...simple concept,
    >but difficult to implement in VB (the getobject issue) w/o using VC++ or
    >building a MTS solution.
    >
    >Any ideas ?
    >
    >Am I way off-base in my expectations here ?
    >
    >Chris
    >
    >Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >>On 6 Jun 2002 11:51:19 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:
    >>
    >>
    >> Has anybody found a way to share an ADO connection across multiple EXEs

    >(all
    >> apps and components done with VB6). I want to be able to have an exe

    >or
    >> activex exe/document create an ADO connection, then be able to get to

    >that
    >> connection from inside other EXEs that are started AFTER the first EXE

    >establishes
    >> the connection. The goal is to have a single connection to the database
    >> per client machine for our application.
    >>
    >> I know that you can't GetObject on a component created in VB.
    >>
    >> I found the RotHook/RotClock example here on DEVX and I'm reading through
    >> that now, but it's getting pretty deep and I'm looking for any other

    solutions
    >> (other than building a C++ component) to solve this issue.
    >>
    >> The final solution may end up being to leave this alone until we move

    >to
    >> NET and then build this application the right way, but for now, we are

    >stuck
    >> w/ the existing design from 5+ years ago that we have to maintain for

    >the
    >> time being.
    >>
    >>The ODBC and OLEDB managers already handle connection pooling if you're

    >using a common
    >>connection
    >>string. I wouldn't recommend sharing a single connection amongst your components.
    >>
    >>
    >>Paul ~~~ pclement@ameritech.net
    >>Microsoft MVP (Visual Basic)

    >



  6. #6
    Paul Clement Guest

    Re: sharing ADO connection across multiple VB EXEs

    On 7 Jun 2002 07:15:45 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:


    Thanks Paul and Michael,

    Still not sure I understand what's going on underneath the hood here...I've
    read the article that Michael posted above as well as everything else I could
    find in MSDN on ado connection pooling.

    My problem is this (more specifically)...the way my current clients application
    is structured it that there are multiple EXEs (standard vb programs) that
    comprise the overall application in logical blocks of business functionality.
    Each EXE is 'fired' from a common toolbar from a standard shell/shellexecute
    statement. The design was implemented years ago by the powers that be at
    the time. The primary reason was the relative size of the application, there
    are TONS of little apps that comprise the overall app, if they were one MDI
    interface, the EXE would be HUGE.

    So...what happens is that when each EXE if opened by the toolbar (which itself
    establishes a connection to the database, each EXE then establishes a connection
    to the database. Every app uses the same user credentials, they are passed
    via the command-line in the shell execute statement (again, not the greatest
    design, but that's the way it is).

    Anyway...once you have mulitple apps open, if you go to the Oracle session
    list, that user is connected to the database multiple times, one for each
    app that's open on their desttop.

    That's what I'd like to get around...figure out a way to open a single connection
    in ADO and have that connection used by all the applications...simple concept,
    but difficult to implement in VB (the getobject issue) w/o using VC++ or
    building a MTS solution.

    Any ideas ?

    Of course if you use a single connection object this will create a blocking situation where only one
    application will be able to access the database at a time. Is this OK in your scenario?


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  7. #7
    Chris Hylton Guest

    Re: sharing ADO connection across multiple VB EXEs


    Michael, already considered that option...to disconnect when not in use...the
    pooling/resource sharing capabilities of OLEDB will keep a connection available
    (at least for awhile) so a reconnect should be fairly fast...but, still not
    an optimal solution...some apps are a quick in and out, some take awhile
    to pull data down or execute a proc...

    Paul, not sure I see what you mean...I might be confused though...if I had
    a single connection object housed in some sort of an activex exe/document
    app...every app would send requests for data through that object...the apps
    are obviously only hit one at a time by the user...

    So, let's say that the toolbar is opened, with buttons for 10 apps on it...the
    toolbar spawns off my custom 'connect' object...then spawns off 2 apps (#1
    and #2)...both in their own space (seperate EXEs)...no EXE would really be
    sending a request through the connection object at the same time, but I guess
    it's possible...for example, if app #1 sent a long running proc through and
    then the user clicked on app #2 and tried to query data...then I guess that
    might create the situation you are referring to (blocking)...where #2 can't
    get to the connection because #1 has it tied up.

    Is that what you are talking about ?

    Makes sense I guess...maybe I do want a seperate ADO connection per app...might
    have to explore the .Close method that Michael brought up...I had already
    tested this a few days ago and it works fine...just requires reconnecting
    w/ every call to a recordset or command object.

    Chris

    Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >On 7 Jun 2002 07:15:45 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >
    > Thanks Paul and Michael,
    >
    > Still not sure I understand what's going on underneath the hood here...I've
    > read the article that Michael posted above as well as everything else

    I could
    > find in MSDN on ado connection pooling.
    >
    > My problem is this (more specifically)...the way my current clients application
    > is structured it that there are multiple EXEs (standard vb programs) that
    > comprise the overall application in logical blocks of business functionality.
    > Each EXE is 'fired' from a common toolbar from a standard shell/shellexecute
    > statement. The design was implemented years ago by the powers that be

    at
    > the time. The primary reason was the relative size of the application,

    there
    > are TONS of little apps that comprise the overall app, if they were one

    MDI
    > interface, the EXE would be HUGE.
    >
    > So...what happens is that when each EXE if opened by the toolbar (which

    itself
    > establishes a connection to the database, each EXE then establishes a

    connection
    > to the database. Every app uses the same user credentials, they are passed
    > via the command-line in the shell execute statement (again, not the greatest
    > design, but that's the way it is).
    >
    > Anyway...once you have mulitple apps open, if you go to the Oracle session
    > list, that user is connected to the database multiple times, one for each
    > app that's open on their desttop.
    >
    > That's what I'd like to get around...figure out a way to open a single

    connection
    > in ADO and have that connection used by all the applications...simple

    concept,
    > but difficult to implement in VB (the getobject issue) w/o using VC++

    or
    > building a MTS solution.
    >
    > Any ideas ?
    >
    >Of course if you use a single connection object this will create a blocking

    situation
    >where only one
    >application will be able to access the database at a time. Is this OK in

    your scenario?
    >
    >
    >Paul ~~~ pclement@ameritech.net
    >Microsoft MVP (Visual Basic)



  8. #8
    Paul Clement Guest

    Re: sharing ADO connection across multiple VB EXEs

    On 7 Jun 2002 13:24:11 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:


    Michael, already considered that option...to disconnect when not in use...the
    pooling/resource sharing capabilities of OLEDB will keep a connection available
    (at least for awhile) so a reconnect should be fairly fast...but, still not
    an optimal solution...some apps are a quick in and out, some take awhile
    to pull data down or execute a proc...

    Once you release the connection through your app, it is returned to the pool. If the connection is
    idle for one minute (OLEDB) it is destroyed.


    Paul, not sure I see what you mean...I might be confused though...if I had
    a single connection object housed in some sort of an activex exe/document
    app...every app would send requests for data through that object...the apps
    are obviously only hit one at a time by the user...

    Correct. If you are using a multi-use ActiveX EXE to share a connection, then blocking will occur at
    the component level under concurrent access.


    So, let's say that the toolbar is opened, with buttons for 10 apps on it...the
    toolbar spawns off my custom 'connect' object...then spawns off 2 apps (#1
    and #2)...both in their own space (seperate EXEs)...no EXE would really be
    sending a request through the connection object at the same time, but I guess
    it's possible...for example, if app #1 sent a long running proc through and
    then the user clicked on app #2 and tried to query data...then I guess that
    might create the situation you are referring to (blocking)...where #2 can't
    get to the connection because #1 has it tied up.

    Is that what you are talking about ?

    Yes.


    Makes sense I guess...maybe I do want a seperate ADO connection per app...might
    have to explore the .Close method that Michael brought up...I had already
    tested this a few days ago and it works fine...just requires reconnecting
    w/ every call to a recordset or command object.

    Probably why it's just better to stick with the connection pooling provided by the driver manager
    services. If you want to reduce the number of connections for each desktop, just make certain each
    application closes a connection after it has been used.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

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