When do I set the connection on ADO?

[ycim]

I am a fairly good VBA programmer, and am now making the jump to Visual Basic. Wanting to be as advanced as I can be, I want to learn ADO funcationality. I have read quite a bit about the flexibility and how to set connection strings. I have read in these forums that you should use the ADODC data object. I have also read others say to connect in each procedure as you need and access the connection. Alternatively, I have seen recommended to set the connection globally when you open your database.

The program I am working on is a lot of database work to control inventory. Therefore, I would be working a lot with SQL and updating/accessing database information. My preference would be to work in Access, but, alas, it is difficult to mass distribute an application as a .mde file in Access.

Does anyone want to comment on how the most efficient and best way is to establish the database connection? And what would be the best mindset to have as I progress forward. It does not make sense to me to open and close the connection with each procedure as I need it. However, I am open to any comments.

 

[jebenson]

The way I see it is that there is no absolute in this - that is, you connect in different ways depending on a number of factors, such as the database being used (Access, SQL Server, etc.), the environment the program is going to inhabit (standalone, network, etc.), and the number of users that will use the program to access the database.

Some general guidelines I use:

Standalone program - open the connection when the program starts and just leave it open until the program ends.

Network program, small number of users (5 or fewer) - open connection and leave it open.

Network program, large number of users - open connection when needed, close as soon as it is no longer needed.

Database used - this is more open-ended, and interacts with the other criteria I mentioned above. The way you connect depends on the machine running the database, the number of concurrent connections the database allows, etc. For example, Access can accomodate 255 concurrent users (I think), but anything over 4 to 6 concurrent users degrades performance so much that the app will be functionally unuseable with more connections. For a standalone program or a networked program with a small (at the very most 20) users, Access can work. But for a large number of users all connectiong to the same database, I would never even consider using Access.

So, there you have my opinion. I'm sure that others will disagree with some or all of my statements, but I hope that this will at least get you started.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[ycim]

That certainly gives me a very good basis to start. Thank you for your very thorough comments. It will be interesting to hear what, if anything, others have to say. I am sure this could be a debate with no real correct answer.
Thanks...

 

[gmmastros]

I agree with jebenson.

In your original post...

It does not make sense to me to open and close the connection with each procedure as I need it.

I will agree that it does seem a little weird to do this. You would think that you are 'wasting' time opening and closing the connection multiple times in the same app. With ADO, connection pooling is used, so the first connection may take a little long (relatively), but all subsequent connections (as long as they have the exact same connection string) will use connection pooling and the connection will be made VERY quickly.

The best part of connection pooling is that it is ON by default. You don't have to do anything to benefit from it.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom