Re: ADO control and SQL Session

thks for sharing.

In my testing, if I open a VBA (not VB) form with ADO control, sp_who
returns 22 session. I closed the form,reopen it, the number of sql session
keep increasing. I can conclude that the when closing the form, sql session
used by those ADO control is not releasd.

my primary concern is how to close those ADO session when I closed my form?

thks



"Mark J. McGinty" wrote:


"HFLo" <HFLo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B8306EB0-A5E2-47AB-8E21-19863D82FC6D@xxxxxxxxxxxxxxxx
I used 2 dif ADO methods in my VBA:

1) I defined my ADO in code Dimc xConnection As New ADODB.Connection
then I close the recordset and connection object before exiting the window

2) I drag and drop the ADO control to a user form.
I close the ADO record set connection

for mehtod 1, the sql connection will be released, but for method 2, the
sql
connection stays. If user re-open the same user form, the number of sql
connection increases more and more.

for method 2, how do I close the ADO connection and relase the SQL
session?

The ADODC control exposes a .recordset property that is a reference to the
underlying ADODB.Recordset object opened by ADODC. This in turn exposes an
..ActiveConnection property so you could, in theory, close these objects
yourself, however, doing so will quite likely cause negative side-effects,
such as error messages and potential data loss.

To minimize side-effects you'd need to force the data control to update (by
setting its .recordset.bookmark property to itself, or otherwise forcing the
record pointer to move) and then unbind it from all the controls on your
form it is bound to (by setting each control's .DataSource property to
Nothing.)

But if you're going to go to all that work, you might as well bind them in
code to your own recordset (Set control.DataSource = MyRecordset, assign
control.DataField = "fieldname", leave control.DataMember empty.) To make
this easy, I typically name each data bound control in a form, the same as
the field to which it will be bound. Then binding can be done by
enumerating either the recordset.Fields collection, or the form.Controls
collection, e.g.;

Dim f As ADODB.Field, ctrl As Control
For each f in rs.Fields
Set ctrl = Me.Contols.Item(f.Name)
If Not ctrl Is Nothing Then
Set ctrl.DataSource = rs
ctrl.DataField = f.Name
End If
Next

That way, any field with name matching a control gets bound; no issues
caused by either fields or controls without matching counter-parts. (Of
course, if you have listboxes, grids or other complex-bound controls, a bit
of extra code to bind them will be required.)

The other option is to write a custom data source, but that's a subject of
its own.


-Mark



.

Relevant Pages

  • Re: ADO control and SQL Session
    ... I defined my ADO in code Dimc xConnection As New ADODB.Connection ... I drag and drop the ADO control to a user form. ... I close the ADO record set connection ... The ADODC control exposes a .recordset property that is a reference to the ...
    (microsoft.public.vb.controls)
  • Re: Newbie - Creating ms access table from Visual Basic
    ... This is one more reason to use ADO library directly instead of control, ... connection, but it will be opened only in a case if your Data control ... If you do not want to use ADO ... >> not Open method of the recordset and option should be adCmdText, ...
    (microsoft.public.vb.database)
  • Re: Newbie - Creating ms access table from Visual Basic
    ... This is one more reason to use ADO library directly instead of control, ... connection, but it will be opened only in a case if your Data control ... If you do not want to use ADO ... >> not Open method of the recordset and option should be adCmdText, ...
    (microsoft.public.vb.database.ado)
  • Re: VB6 Winsock action on Server
    ... With the introduction on your part of ADO and DBMS. ... to be used to connect and manage data flow between client and DB? ... I think the issue is namely how to control concurrent access. ...
    (microsoft.public.vb.general.discussion)
  • Re: VB6 - Using Unbound Controls
    ... The VB6 Forms use Data Controls and ADO. ... I've developed Access Apps w/ Unbound ... Controls using DAO for almost 6 years, ... selected data from the Field data saved by the Data Control in the VB6 ...
    (microsoft.public.vb.general.discussion)