Close an active ODBC connection

[Jeff]

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?

 

[Joel]

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]

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]

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]

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

Thanks anyway.

Jeff