I've got a connection to a SQL database that I create when I update the
source data. I keep it open to run multiple queries from the same source but
I want to close it when I close the workbook.

How can I close an active connection that was created and maintained in a
different sub-routine?

How are you keeping it opened? Is ther are Public or Static variable that
you are using?

If ther is a variable associated with the connection then simply create a
workbook close event, close the conection variable, and then set the
Connection variable to equal nothing.

"Jeff" wrote:

> I've got a connection to a SQL database that I create when I update the
> source data. I keep it open to run multiple queries from the same source but
> I want to close it when I close the workbook.
>
> How can I close an active connection that was created and maintained in a
> different sub-routine?
>
>

I have a subroutine that creates the connection. The connection can take a
little bit to open (I'm assuming it's a network thing) so I don't want to
keep opening and closing it. That's why I thought it would be easier to open
it once and close it when I'm done with it.

The programmer does it this way through Visual Basic for the same reasons.


"Joel" wrote:

> How are you keeping it opened? Is ther are Public or Static variable that
> you are using?
>
> If ther is a variable associated with the connection then simply create a
> workbook close event, close the conection variable, and then set the
> Connection variable to equal nothing.
>
> "Jeff" wrote:
>
> > I've got a connection to a SQL database that I create when I update the
> > source data. I keep it open to run multiple queries from the same source but
> > I want to close it when I close the workbook.
> >
> > How can I close an active connection that was created and maintained in a
> > different sub-routine?
> >
> >

Are you sure you are keeping the connection open after the macro finishes?
VBA doesn't normally retain connections when a macro ends. That is why I'm
asking? If it is automatically closing when the macro finishes, then you
don't need to close it again. You'll just get an error.

"Jeff" wrote:

> I have a subroutine that creates the connection. The connection can take a
> little bit to open (I'm assuming it's a network thing) so I don't want to
> keep opening and closing it. That's why I thought it would be easier to open
> it once and close it when I'm done with it.
>
> The programmer does it this way through Visual Basic for the same reasons.
>
>
> "Joel" wrote:
>
> > How are you keeping it opened? Is ther are Public or Static variable that
> > you are using?
> >
> > If ther is a variable associated with the connection then simply create a
> > workbook close event, close the conection variable, and then set the
> > Connection variable to equal nothing.
> >
> > "Jeff" wrote:
> >
> > > I've got a connection to a SQL database that I create when I update the
> > > source data. I keep it open to run multiple queries from the same source but
> > > I want to close it when I close the workbook.
> > >
> > > How can I close an active connection that was created and maintained in a
> > > different sub-routine?
> > >
> > >

I got it working and the connection speed isn't prohibitive so I don't need
to solve this problem.

Thanks anyway.

Jeff

"Joel" wrote:

> Are you sure you are keeping the connection open after the macro finishes?
> VBA doesn't normally retain connections when a macro ends. That is why I'm
> asking? If it is automatically closing when the macro finishes, then you
> don't need to close it again. You'll just get an error.
>
> "Jeff" wrote:
>
> > I have a subroutine that creates the connection. The connection can take a
> > little bit to open (I'm assuming it's a network thing) so I don't want to
> > keep opening and closing it. That's why I thought it would be easier to open
> > it once and close it when I'm done with it.
> >
> > The programmer does it this way through Visual Basic for the same reasons.
> >
> >
> > "Joel" wrote:
> >
> > > How are you keeping it opened? Is ther are Public or Static variable that
> > > you are using?
> > >
> > > If ther is a variable associated with the connection then simply create a
> > > workbook close event, close the conection variable, and then set the
> > > Connection variable to equal nothing.
> > >
> > > "Jeff" wrote:
> > >
> > > > I've got a connection to a SQL database that I create when I update the
> > > > source data. I keep it open to run multiple queries from the same source but
> > > > I want to close it when I close the workbook.
> > > >
> > > > How can I close an active connection that was created and maintained in a
> > > > different sub-routine?
> > > >
> > > >