Connection Object to SQL Server 1

[seaport]

In my Access 2000 db, I use a global ADODB connection object to connect to a SQL server backend database. Right now in every function/subroutine that needs the connection to the sql server, I openned the connection at the start of the procedure and close the connection at the end.

I wonder whether it is necessary to close the connection object in every procedure. What are sides effects of not closing only one connection?

There are three options to me.
First is the way I am doing right now. Closing the connection in every procedure.
Second is just the opposite - No closing in the procedure.
Third is the middle ground. I may close the connection in the form_close event.

This is somehow a best-practice question. Any feedback is appreciated.

Thanks in advance.

Seaport

 

[jrbarnett]

For what its worth, I open a connection object at the start of the program and close it on exiting the app.
I do it this way so that my SQL server isn't continually authenticating connections as the user uses different parts of my app, and that there is only one open connection per user (useful if your SQL server is licensed per seat rather than processor basis).

Even if you don't close your connections within your procedures, I'd certainly close it when exiting your application, or you end up with excess open connections in your server.

I use a shared connection by having a dedicated module with a private connection object, then public parameterised stub functions in there called from elsewhere returning recordsets to the calling app with validation code before execution (so that even if somebody gets to the debug window in my MDE user copy, they can't execute code off the connection object without bypassing my validation code).

John

 

[seaport]

Thanks, John.That is the approach that I would like to take.

By the way, I am using Access 2000. Can a user get to the debug window in a MDE? As I know, when a user runs into an error, the Debug button is greyed out.

Seaport

 

[JoeAtWork]

If you are always using the exact same connection string, it shouldn't matter if you close and reopen your connection, because SQL Server uses connection pooling. That means after you "close" it, SQL Server keeps it open for a while and if another connection is made with the same connection string, it just reuses the previously "closed" one.

You can't go into debug in an MDE because the code is stripped out of MDE's.

 

[jrbarnett]

Joe/seaport - point taken about debug mode in an MDE file, but even if a database is MDEd, you can still issue commands from the immediate window. The success will depend on how secure the code is.

Best option is to use the Startup options to disable the Special keys on production copies of your application, which stops opening the VB editor, database window etc.

John