1 connection?

[Sander Verhagen]

Hi,


Using sqloledb as a provider for ADO; using exactly 1 connection to a MSDE
Server.

I'm sure many of you could imagine scenarios in which I'm walking through a
recordset, processing each of the records. Also imagine that this particular
processing again involves some database activity.

This inner database activity, for the mentioned processing, might in some
circumstances be in violation with the outer activity, for the mentioned
recordset, since the recordset is still using my 1 connection.

Isn't it generally bad practice (as a proposed solution to this) to use as
many connections as I have recordsets that are opened at that time? Does the
number of these connections map to the maximum number of "concurrent users"
that MS talks about in the MSDE specs? Do you guys solve this differently?

Thanks for thinking with me!

Greetings,


Sander Verhagen
[ (e-mail address removed) ]

 

[Graham R Seach]

The rule is to only use as many connections as you *actually* need. This
does not mean creating a connection for every recordset or command object.

The *connection* is the *pipe* through which data passes, and that pipe can
have specific properties associated with it. Transactions are also managed
at connection-level. So, you will probably want to create a new connection
only if the new connection is somehow different from the one you already
have, or if you are going to be running a separate set of transactions.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia