0

I have a question about the ADODB.connection.

I use ADODB connection to connect to Access in my Excel application. Do I need to close the connection manually, or will it be closed automatically when my Excel application is closed?

If I need to do it manually, then when and where? Can I write the code in the Workbook_BeforeClose event? I open the connection in the Workbook_Open event, and the connection object is declared to be a global var.

Community
  • 1
  • 1
Guoliangcai
  • 57
  • 5
  • 1
    When you close Excel then the connection object will go out of scope, but it's good practice to explicitly close it in your code. – Tim Williams Feb 22 '17 at 06:42
  • 1
    Possible duplicate of [When should an Excel VBA variable be killed or set to Nothing?](http://stackoverflow.com/q/19038350/11683) – GSerg Feb 22 '17 at 08:31
  • This might interesting for you... http://stackoverflow.com/questions/3217014/how-to-securely-store-connection-string-details-in-vba?noredirect=1&lq=1 I use this method to connect to ADODB.Connection – Martin Router King Feb 22 '17 at 09:18
  • OK,thanks very much for your answers. – Guoliangcai Feb 22 '17 at 09:24

1 Answers1

-1

I believe if you don't explicitly close the connection, the connection will stay open even if excel exits.

Unfortunately VBA is not that intelligent to close every connection etc when an object goes out of scope. But still, it it was, the best practice is to close every stream and connection, whenever you don't need it anymore, and of course, upon exiting/closing the workbook, it is a good idea to double-check whether the connection is open, and if so, close it. That just makes your application even more robust and reliable.

So what I suggest to you is that you close the connection whenever some r/w operation is done. Don't just leave a connection open while the app is idle (because what if excel or the system freezes?). So keep it open just as long as necessarily needed, but to be sure make double-check in the Workbook_BeforeClose eventhandler.

ThomasMX
  • 1,643
  • 2
  • 19
  • 35
  • 1
    `Unfortunately VBA is not that intelligent to close every connection etc when an object goes out of scope` - on contrary, that is [exactly what VBA does](http://stackoverflow.com/a/19038890/11683). – GSerg Feb 22 '17 at 08:34
  • Thanks :) i think now i got the idea. – Guoliangcai Feb 22 '17 at 09:25