1.  My application pre-connects to an Oracle database.  In other words, it 
prompts the user for an Oracle user ID and password in a nice little Access 
form.  

2.  In my pre-connect module (on button click), I set the connect string for 
the database, and use the .OpenDatabase method to connect.

3.  Next, I cleanup by closing the database object and setting it to 
"Nothing".   Through some miracle I don't understand, the connection to 
Oracle remains open even though I closed everything.

My question: when my MS Access application closes, how do I tell the Oracle 
connection that I'm finished?  Do I need to?  I'd like to be sure I'm 
cleaning up as best I can.

Thanks.

hi David,

David Mueller wrote:
> My question: when my MS Access application closes, how do I tell the Oracle 
> connection that I'm finished?  Do I need to?  I'd like to be sure I'm 
> cleaning up as best I can.
ODBC connections are cached, afaik only closing Access itself drops the 
connection.


mfG
--> stefan <--

Setting the connection to Nothing cleans up the VBA connection object,
but does nothing to the server connection. I don't know Oracle, but
isn't there some way to issue a command to Oracle itself to drop the
connection before you set the VBA object to Nothing?

--Mary

On Mon, 27 Mar 2006 04:50:02 -0800, David Mueller
 wrote:

>
>1.  My application pre-connects to an Oracle database.  In other words, it 
>prompts the user for an Oracle user ID and password in a nice little Access 
>form.  
>
>2.  In my pre-connect module (on button click), I set the connect string for 
>the database, and use the .OpenDatabase method to connect.
>
>3.  Next, I cleanup by closing the database object and setting it to 
>"Nothing".   Through some miracle I don't understand, the connection to 
>Oracle remains open even though I closed everything.
>
>My question: when my MS Access application closes, how do I tell the Oracle 
>connection that I'm finished?  Do I need to?  I'd like to be sure I'm 
>cleaning up as best I can.
>
>Thanks.
>

Mary,
The 'disconnect' command will disconnect you from an Oracle database in 
SQL/Plus. I suppose you could issue the same command through an Access 
PassThrough query, although I have never tried it.

-- 
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List: 
http://home.bendbroadband.com/conradsystems/accessjunkie.html



"Mary Chipman [MSFT]"  wrote in message 
news:6qgg225fj15urnsfkp8fv5smaanjhfkl7p@4ax.com...
> Setting the connection to Nothing cleans up the VBA connection object,
> but does nothing to the server connection. I don't know Oracle, but
> isn't there some way to issue a command to Oracle itself to drop the
> connection before you set the VBA object to Nothing?
>
> --Mary
>
> On Mon, 27 Mar 2006 04:50:02 -0800, David Mueller
>  wrote:
>
>>
>>1.  My application pre-connects to an Oracle database.  In other words, it
>>prompts the user for an Oracle user ID and password in a nice little 
>>Access
>>form.
>>
>>2.  In my pre-connect module (on button click), I set the connect string 
>>for
>>the database, and use the .OpenDatabase method to connect.
>>
>>3.  Next, I cleanup by closing the database object and setting it to
>>"Nothing".   Through some miracle I don't understand, the connection to
>>Oracle remains open even though I closed everything.
>>
>>My question: when my MS Access application closes, how do I tell the 
>>Oracle
>>connection that I'm finished?  Do I need to?  I'd like to be sure I'm
>>cleaning up as best I can.
>>
>>Thanks.
>>

Wouldn't you have to issue the disconnect through the same connection?
If it's opened in VBA code, closing it through a pass-through query
probably won't work. Just guessing since I'm Oracle-challenged :-)

--Mary

On Tue, 28 Mar 2006 07:32:20 -0600, "Lynn Trapp"
 wrote:

>Mary,
>The 'disconnect' command will disconnect you from an Oracle database in 
>SQL/Plus. I suppose you could issue the same command through an Access 
>PassThrough query, although I have never tried it.

The disconnect command is a property of the SQL/Plus application, not an 
Oracle function.  I can't think of any way short of closing Access to 
drop the connection if you're using linked tables.  Unless, of course, 
you're trying to keep the connection open, in which case Access will 
close it for you at the least opportune moment. / Mattias

From: Lynn Trapp, on 3/28/2006 8:32 AM:
> Mary,
> The 'disconnect' command will disconnect you from an Oracle database in 
> SQL/Plus. I suppose you could issue the same command through an Access 
> PassThrough query, although I have never tried it.
>

Mary,
That's most likely correct. Like I said, I've never tried to use the 
disconnect command in Access, so was shooting in the dark.

You're too hard on yourself, Mary. You could own Oracle in a couple of 
weeks. <g>

-- 
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List: 
http://home.bendbroadband.com/conradsystems/accessjunkie.html



"Mary Chipman [MSFT]"  wrote in message 
news:2gqv229b8fbrij6eigdigivrmvek0884ai@4ax.com...
> Wouldn't you have to issue the disconnect through the same connection?
> If it's opened in VBA code, closing it through a pass-through query
> probably won't work. Just guessing since I'm Oracle-challenged :-)
>
> --Mary
>
> On Tue, 28 Mar 2006 07:32:20 -0600, "Lynn Trapp"
>  wrote:
>
>>Mary,
>>The 'disconnect' command will disconnect you from an Oracle database in
>>SQL/Plus. I suppose you could issue the same command through an Access
>>PassThrough query, although I have never tried it.

> The disconnect command is a property of the SQL/Plus application, not an 
> Oracle function.  I can't think of any way short of closing Access to drop 
> the connection if you're using linked tables.

It was just a shot in the dark.

> Unless, of course, you're trying to keep the connection open, in which 
> case Access will close it for you at the least opportune moment.

ROFL...that is so true.

-- 
Lynn Trapp
MS Access MVP

I'm pretty sure I could issue a command to Oracle before closing the VBA 
object, but in this case, I need the Oracle server connection to remain open 
as long as the user has Access open.

When Access closes, I'm wondering if I'm [Access is] leaving open 
connections on the server side.  If so, it hasn't hurt anything for a couple 
years now, and the DBAs don't call me.  

I'll just proceed business as usual.  Thanks all.




"Mary Chipman [MSFT]" wrote:

> Setting the connection to Nothing cleans up the VBA connection object,
> but does nothing to the server connection. I don't know Oracle, but
> isn't there some way to issue a command to Oracle itself to drop the
> connection before you set the VBA object to Nothing?
> 
> --Mary
> 
> On Mon, 27 Mar 2006 04:50:02 -0800, David Mueller
>  wrote:
> 
> >
> >1.  My application pre-connects to an Oracle database.  In other words, it 
> >prompts the user for an Oracle user ID and password in a nice little Access 
> >form.  
> >
> >2.  In my pre-connect module (on button click), I set the connect string for 
> >the database, and use the .OpenDatabase method to connect.
> >
> >3.  Next, I cleanup by closing the database object and setting it to 
> >"Nothing".   Through some miracle I don't understand, the connection to 
> >Oracle remains open even though I closed everything.
> >
> >My question: when my MS Access application closes, how do I tell the Oracle 
> >connection that I'm finished?  Do I need to?  I'd like to be sure I'm 
> >cleaning up as best I can.
> >
> >Thanks.
> >
>

Access should take care of it, unless you drop a network connection. 
Your DBA has a system view that shows count of dropped connections per 
username.  They would be able to tell you, unless you decide it is 
better to let sleeping dogs lie.

From: David Mueller, on 4/4/2006 1:14 PM:
> When Access closes, I'm wondering if I'm [Access is] leaving open 
> connections on the server side.  If so, it hasn't hurt anything for a couple 
> years now, and the DBAs don't call me.

Yeah, right. That and the Brooklyn Bridge :-)

On Mon, 3 Apr 2006 08:52:14 -0500, "Lynn Trapp"
 wrote:

>Mary,
>That's most likely correct. Like I said, I've never tried to use the 
>disconnect command in Access, so was shooting in the dark.
>
>You're too hard on yourself, Mary. You could own Oracle in a couple of 
>weeks. <g>

ROFL...If I can learn Oracle, then you could do it in a heartbeat.

-- 
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List: 
http://home.bendbroadband.com/conradsystems/accessjunkie.html



"Mary Chipman [MSFT]"  wrote in message 
news:58u732dd1p782mmtk3ecl1od44i7qnsj7j@4ax.com...
> Yeah, right. That and the Brooklyn Bridge :-)
>
> On Mon, 3 Apr 2006 08:52:14 -0500, "Lynn Trapp"
>  wrote:
>
>>Mary,
>>That's most likely correct. Like I said, I've never tried to use the
>>disconnect command in Access, so was shooting in the dark.
>>
>>You're too hard on yourself, Mary. You could own Oracle in a couple of
>>weeks. <g>