ADODB.Connection from a Class[B/]

[whool]

I have a class that executes a SQL stored procedure, supplying it with parameters. The sp is used for updating individual DB records and is executed every time a user clicks an update button; quite often.

My question is, where should I open the DB connection? I'm relucted to use an existing open public Connection
from a Module (if for no other reason than it is not conventional), but the alternative is to open the DB connection for every new instance of the class, ie every time a user updates a record. However, doing it this way I'm conerned about the overhead involved in continually opening and closing a DB connection.

I thought I might be able to pass an already open DB connection to a Connection property in the class but that doesn't seem possible.

Could someone advise me on the correct appraoch to DB connectivity in classes.

Thanks in advance,
Yael

 

[NickISD]

Yael,
It's a matter of your own personal preference whether you open the DB and keep it open until the user logs out or open and close on demand. The upside of this is that all modules etc can have the connection and there is no delay in having to make another connection. The downside is that if you have a lot of users logging in to your DB then some or all of them could log in and have the connection open all day putting a load on the server to maintain all the open connections. Personally I go for the former and open the DB using a class module that has a public variable for the database so that all my forms, modules etc can access the DB.

Hope this helps.

Nick W.

 

[whool]

Thanks for comments Nick. It makes sence what you say however this approach would not be appropriate if the Class is in a DLL and is to be used by many and varied applications; all future applications which use that class would be committed to creating a Public connection object with the same name. Should a DLL have that much sway over the programmer writting an application which uses it? I wouldn't have thought so.

Guess I should probably do a little reading.

Yael