? about adp and connections

[Ellen Manning]

Using A2K and adp. Is it necessary to open a connection (to SQL
Server 2000) everytime you call a stored procedure or open a
recordset? Or will opening a connection one time do, like at
Form_Open?

I have a number of combo boxes that call the following public sub when
the NotInList event is fired:

Public Sub InsertItem(strCommandText As String, strParameterName As
String, _
adType As DataTypeEnum, lngSize As Long, strNewData As String)
On Error GoTo InsertItem_Error

Dim cmdInsert As ADODB.Command
Dim prm As Parameter
Const conQuote = """"

Set cnn = New ADODB.Connection
cnn.ConnectionString = GetConnectString
cnn.Open

Set cmdInsert = New ADODB.Command
With cmdInsert
Set .ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = conQuote & strCommandText & conQuote
End With

Set prm = _
cmdInsert.CreateParameter(strParameterName, adType,
adParamInput, lngSize, strNewData)
cmdInsert.Parameters.Append prm

cmdInsert.Execute

Set cnn = Nothing

Exit Sub

....<error processing snipped>...
I set the connection, call the stored procedure, then close the
connection. I open recordsets in other parts of the form as
well...opening and closing the connection. Is it necessary to make
all these connections or can I just make one at Form_Open and close it
at Form_Close?

[Lyle Fairfield]

ma**********@hotmail.com (Ellen Manning) wrote in
news:da*************************@posting.google.co m:

Using A2K and adp. Is it necessary to open a connection (to SQL
Server 2000) everytime you call a stored procedure or open a
recordset? Or will opening a connection one time do, like at
Form_Open?

I have a number of combo boxes that call the following public sub when
the NotInList event is fired:

Public Sub InsertItem(strCommandText As String, strParameterName As
String, _
adType As DataTypeEnum, lngSize As Long, strNewData As String)
On Error GoTo InsertItem_Error

Dim cmdInsert As ADODB.Command
Dim prm As Parameter
Const conQuote = """"

Set cnn = New ADODB.Connection
cnn.ConnectionString = GetConnectString
cnn.Open

Set cmdInsert = New ADODB.Command
With cmdInsert
Set .ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = conQuote & strCommandText & conQuote
End With

Set prm = _
cmdInsert.CreateParameter(strParameterName, adType,
adParamInput, lngSize, strNewData)
cmdInsert.Parameters.Append prm

cmdInsert.Execute

Set cnn = Nothing

Exit Sub

...<error processing snipped>...
I set the connection, call the stored procedure, then close the
connection. I open recordsets in other parts of the form as
well...opening and closing the connection. Is it necessary to make
all these connections or can I just make one at Form_Open and close it
at Form_Close?

I tend to open a connection a new connection for all code interaction with
the db. Can you keep one open? Sure you can, and you can use it as long as
it's in scope. And of course you can use CurrentProject.Connection, but it is
painfully slow; I have found that opening a simple connection using the
BaseConnectionString is much faster. I'm assuming you are doing this using
your GetConnectString function.

I use a Public Function to give me a quickie connection:

Public Function MinimalADODBConnection(Optional ByVal CursorLocation As
ADODB.CursorLocationEnum = adUseServer) As ADODB.Connection
Set MinimalADODBConnection = New ADODB.Connection
With MinimalADODBConnection
.ConnectionString = CurrentProject.BaseConnectionString
.CursorLocation = CursorLocation
.Open
End With
End Function

and use it thus:

Set r = New ADODB.Recordset
With r
.ActiveConnection = MinimalADODBConnection()
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Blah Blah"

I believe it is not necessary to release ADO object references and that in
this code there is no need or reason to set the connection to nothing
(there's no object variable to set to nothing anyway).

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Ellen Manning]

Lyle Fairfield <Mi************@Invalid.Com> wrote in message news:<Xn*******************@130.133.1.4>...

ma**********@hotmail.com (Ellen Manning) wrote in
news:da*************************@posting.google.co m:

Using A2K and adp. Is it necessary to open a connection (to SQL
Server 2000) everytime you call a stored procedure or open a
recordset? Or will opening a connection one time do, like at
Form_Open?

I have a number of combo boxes that call the following public sub when
the NotInList event is fired:

Public Sub InsertItem(strCommandText As String, strParameterName As
String, _
adType As DataTypeEnum, lngSize As Long, strNewData As String)
On Error GoTo InsertItem_Error

Dim cmdInsert As ADODB.Command
Dim prm As Parameter
Const conQuote = """"

Set cnn = New ADODB.Connection
cnn.ConnectionString = GetConnectString
cnn.Open

Set cmdInsert = New ADODB.Command
With cmdInsert
Set .ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = conQuote & strCommandText & conQuote
End With

Set prm = _
cmdInsert.CreateParameter(strParameterName, adType,
adParamInput, lngSize, strNewData)
cmdInsert.Parameters.Append prm

cmdInsert.Execute

Set cnn = Nothing

Exit Sub

...<error processing snipped>...
I set the connection, call the stored procedure, then close the
connection. I open recordsets in other parts of the form as
well...opening and closing the connection. Is it necessary to make
all these connections or can I just make one at Form_Open and close it
at Form_Close?

I tend to open a connection a new connection for all code interaction with
the db. Can you keep one open? Sure you can, and you can use it as long as
it's in scope. And of course you can use CurrentProject.Connection, but it is
painfully slow; I have found that opening a simple connection using the
BaseConnectionString is much faster. I'm assuming you are doing this using
your GetConnectString function.

I use a Public Function to give me a quickie connection:

Public Function MinimalADODBConnection(Optional ByVal CursorLocation As
ADODB.CursorLocationEnum = adUseServer) As ADODB.Connection
Set MinimalADODBConnection = New ADODB.Connection
With MinimalADODBConnection
.ConnectionString = CurrentProject.BaseConnectionString
.CursorLocation = CursorLocation
.Open
End With
End Function

and use it thus:

Set r = New ADODB.Recordset
With r
.ActiveConnection = MinimalADODBConnection()
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Blah Blah"

I believe it is not necessary to release ADO object references and that in
this code there is no need or reason to set the connection to nothing
(there's no object variable to set to nothing anyway).

Thanks for your reply. I have a situation where I sporadically get
"3709 - The connection cannot be used to perform this operation. It
is either closed or invalid in this context." Since I open and close
connections all over the place in my adp, I thought maybe I'd
forgotten to close a connection somewhere. Then I got to wondering
why I open and close so many times and maybe I could just get away
with one open and one close. Maybe my problem lies elsewhere. Will
do some more checking.