ADODB connection question. What is better ? 3

[vamoose]

I am using MSSql 2000 as my database and MSAccess 2000 as my interface (front end). This Access front end has many forms for data entry and displaying information. I am currently using an ADODB connection, a few dozen times I must say, to the SQL database. Each time I use the connection I set it up using Dim and Set, define the driver, open the connection, do my business and when I am done I close and clear the connection.
So here is the question: Is the "best way" to do this. Or, can I simply define and open the connection when Access opens, conduct all my business using the same connection many times just by changing the SQL statement, then close the connection as Access is shut down thereby saving all the openings, closings and VBA code ? What is the best practice and best for the SQL database

Thanks for any suggestions.

 

[AlexCuse]

It is best to close the connection each time you are done with it.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[jsteph]

...and it's my belief, someone correct me if incorrect, that the server caches that connection so if it's re-opened (within some timeout period) that it isn't much of a burden at all on the server to re-establish it.

In my AS/400 days, there was a setting called 'lazy close', so when you'd close the connection from a client machine, the server was purposefully 'lazy' about actually closing it because you may want to reopen it, and the mere act of closing it took some server resources. If you unchecked this setting, then it would take noticeably longer to open a connection in your code. But that's the 400, I don't know exactly how sql-server handles this.
--Jim

 

[gmmastros]

Jim,

What you just described is similar to Connection Pooling within ADO. Because of connection pooling, it is very fast to re-establish a connection to the database.

-George

"the screen with the little boxes in the window." - Moron