? 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(strC ommandText As String, strParameterNam e As
String, _
adType As DataTypeEnum, lngSize As Long, strNewData As String)
On Error GoTo InsertItem_Erro r

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

Set cnn = New ADODB.Connectio n
cnn.ConnectionS tring = GetConnectStrin g
cnn.Open

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

Set prm = _
cmdInsert.Creat eParameter(strP arameterName, adType,
adParamInput, lngSize, strNewData)
cmdInsert.Param eters.Append prm

cmdInsert.Execu te

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**********@ho tmail.com (Ellen Manning) wrote in
news:da******** *************** **@posting.goog le.com:

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(strC ommandText As String, strParameterNam e As
String, _
adType As DataTypeEnum, lngSize As Long, strNewData As String)
On Error GoTo InsertItem_Erro r

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

Set cnn = New ADODB.Connectio n
cnn.ConnectionS tring = GetConnectStrin g
cnn.Open

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

Set prm = _
cmdInsert.Creat eParameter(strP arameterName, adType,
adParamInput, lngSize, strNewData)
cmdInsert.Param eters.Append prm

cmdInsert.Execu te

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
BaseConnectionS tring is much faster. I'm assuming you are doing this using
your GetConnectStrin g function.

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

Public Function MinimalADODBCon nection(Optiona l ByVal CursorLocation As
ADODB.CursorLoc ationEnum = adUseServer) As ADODB.Connectio n
Set MinimalADODBCon nection = New ADODB.Connectio n
With MinimalADODBCon nection
.ConnectionStri ng = CurrentProject. BaseConnectionS tring
.CursorLocation = CursorLocation
.Open
End With
End Function

and use it thus:

Set r = New ADODB.Recordset
With r
.ActiveConnecti on = MinimalADODBCon nection()
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimisti c
.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******* ************@13 0.133.1.4>...

ma**********@ho tmail.com (Ellen Manning) wrote in
news:da******** *************** **@posting.goog le.com:

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(strC ommandText As String, strParameterNam e As
String, _
adType As DataTypeEnum, lngSize As Long, strNewData As String)
On Error GoTo InsertItem_Erro r

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

Set cnn = New ADODB.Connectio n
cnn.ConnectionS tring = GetConnectStrin g
cnn.Open

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

Set prm = _
cmdInsert.Creat eParameter(strP arameterName, adType,
adParamInput, lngSize, strNewData)
cmdInsert.Param eters.Append prm

cmdInsert.Execu te

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
BaseConnectionS tring is much faster. I'm assuming you are doing this using
your GetConnectStrin g function.

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

Public Function MinimalADODBCon nection(Optiona l ByVal CursorLocation As
ADODB.CursorLoc ationEnum = adUseServer) As ADODB.Connectio n
Set MinimalADODBCon nection = New ADODB.Connectio n
With MinimalADODBCon nection
.ConnectionStri ng = CurrentProject. BaseConnectionS tring
.CursorLocation = CursorLocation
.Open
End With
End Function

and use it thus:

Set r = New ADODB.Recordset
With r
.ActiveConnecti on = MinimalADODBCon nection()
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimisti c
.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.