use existing connection or create new

[Lauren Quantrell]

I'm wondering which is the best approach using an Access2K front end
and a SQL Server 2K backend.
I have a stored procedure running three INSERT INTO statements that
inserts records into three different tables.

Should I use the ADP project connection as set through the Access
Connection window:
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection

Or, should I create a new connection, then execute the command, then
close the connection.

Does it make any difference at all?
Would it make a difference as far as minimizing blocking errors to do
it one way or the other?

Help is appreciated.
lq

[Trevor Best]

Lauren Quantrell wrote:

I'm wondering which is the best approach using an Access2K front end
and a SQL Server 2K backend.
I have a stored procedure running three INSERT INTO statements that
inserts records into three different tables.

Should I use the ADP project connection as set through the Access
Connection window:
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection

Or, should I create a new connection, then execute the command, then
close the connection.

Does it make any difference at all?
Would it make a difference as far as minimizing blocking errors to do
it one way or the other?

Help is appreciated.
lq

I'd use one connection wrapped in a transaction, that way I can roll
back everything if one fails.

The only time I'd use another connection is to be outside of the
transaction, e.g for error logging purposes, I found out the hard way
that my error log got rolled back when I rolled back everything else.
--
But why is the Rum gone?