Re: ADO control and SQL Session


"HFLo" <HFLo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B89D1AF3-0719-48B5-AF49-0D737A298E8E@xxxxxxxxxxxxxxxx
thks for sharing.

.... rrriiigghhttt

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.

Patient: Doctor it hurts when I do this.
Doctor: Don't do that.

Translation: If ADODC doesn't do what you want, bind controls on your form
to a recordset (which, you've found, does do what you want) instead. That's
one option. I demonstrated it with code.


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

[Take 2, cranking 'dumb-it-down' knob to 11]

The "ADO control" (aka ADOD*C*, 'C' being short for 'control') internally
uses the ADODB.Recordset object (which is sometimes referred to as just
'Recordset', just so you know.)

In many [but not all] cases, ADODB.Recordset internally uses the
ADODB.Connection object -- when this is the case, you can get to the
Recordset's connection object, using its ActiveConnection property. Once
you have the connection object, you can 'close' it, by calling that object's
deceptively named '.Close' method.

I realize this is confusing, because you're used to declaring/initializing a
connection variable first, opening it, and then using it to create/open a
recordset, but here's the tricky thing (stay with me if you can): it
doesn't matter how you acquired the connection object, it still works the
same way! So let's connect the dots, shall we?

Dim cn as ADODB.Connection
Set cn = ADODC1.Recordset.ActiveConnection
cn.Close

Sadly, I'm must warn that for the sake of safety, you should *not* use the
example above, because of potential consequences that you seem unable or
unwilling to try to grasp. This will tend to make the task of considering
those consequences somewhat painful; mitigating their risks thus becomes
downright problematic.


-Mark



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: D7 errors with complex dataset command text
    ... I have a feeling it's some MDAC ... I did try changing this to a view but SQL won't let me update it if ... to lock up on me when I try to set the connection object back to connected ...
    (borland.public.delphi.database.ado)
  • ADO connection question
    ... I'm using an ADO 2.8 connection to a SQL Server 2000 database from a VBA ... a connection object so many times. ...
    (microsoft.public.data.ado)
  • Re: 2 ways to close a connection
    ... The Connection object does not in fact represent a connection to SQL ... turning connection pooling off and repeating ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... SQL CE/Mobile has a background thread that flushes the changes ... they run for long hours and we have not seen memory leaks so far. ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... So it is a kind of asynchronous commit which happens every 10 ... I am not sure of this claim as we have many applications built on SQL CE ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)