Database Connection??? Please Advice

[tomtan]

Hi,

I Using VB 6.0 with ADO.

I need some Opinions regarding database connections.
DB.ConnectionString = ConString
DB.Open

I always design my programs when I run my application I will open the database connection, I will only close the connection when I exit from my application. But for my recordset, when I open a recordset for Inserting/updating records and I will close the recordset immediately after finish Inserting/updating the records.

But I got of Information that if I always maintain the database connections open(multi-user Environment), I will slow down the server in a way if a lot of users are connected .

Is it advisable to maintain the database connection open until I exit from my application?

Please advice.

Thanks.

[Clunietp]

For a regular client/server app, it is usually best to keep your DB connection objects at module or global scope.

Disconnecting and reconnecting to your DB is slow and resource intensive on both ends, so you are usually better off just keeping an open connection at high scope

[tomtan]

Thanks for your advice.. Clunietp

Anybody have more oppinions..

[sanon]

As you mentioned about multi-user, it's good idea to not use resource, which is in this case DB, on the time. Moreover, it depends on how your program works as well. For example, if you just load data to a recordset for display on your form one time in form load event, you don't have any reasons to connect to the DB any more. Moreover, if your program wants to have capability of editing records, you can do that by connect to the DB again. It's true that if you often connect and disconnect the DB, it will be slow, but, again, if your program doesn't expect often editing, you don't have to connect to the DB all the time. It waste resources and may cause your DB server crash.