Closing an Access Database ( ldb )- compact and repair

[Rick Lipkin]

Marc

No, if I recall your issue was with SqlLite ?? not closing when you quit your app. This has to do with how a recordset is defined and created at runtime and as Enrico mentions the runtime scope of the recordset object.

Two ways you can create a recordset is to Open() with the Sql statement and a connection string, OR a pre-opened global connection OBJECT.

What I did not know is that when working with Ms Access .. the .ldb does not necessarily go away when you Close() a recordset specifically created with a connection string. So as you open and close many recordsets, you accumulate many 'active' connections.

Where this came into focus for me was that I created a Repair and Compact utility for Ms Access ( .mdb ) and to work properly, the .ldb MUST be closed. I searched and searched for any possible recordsets I potentially did not Close() and found none.. so if there are no tables open .. why did the .ldb not go away ?

I went back thru my code and 'found and replaced' all my Recordsets that opened with a connection string and replaced that string parameter with an active global connection object I created at the top of my program.

NOW .. when I go to my 'repair and compact' routine .. all I have to do is close the global connection oCn:CLose() and the .ldb goes away... run my routine and re-establish the global connection oCn... and the application continues to run fine.

Again .. this is all well and good as long as nothing happens to interrupt the global connection object.

This has been an interesting learning experience ..

Rick Lipkin

[nageswaragunupudi]

Rick,

Is it the same problem that I had in this topic?
I couldn't delete the file brcause it was not completely closed.

http://forums.fivetechsupport.com/viewtopic.php?f=3&t=26787

Mr Mark

Unless we test with simplest possible code, we can not figure out whats going wrong and why. (Note: This is what one can learn from Mr. EMG's postings)

Please see the code I am about to post now in the thread you referred above. We can erase the file without waiting after closing the connection.

[Enrico Maria Giordano]

NageswaraRao,

I got your point. I am working on this.
It also means "all references in the memory" to this recordset object should go out of scope. Right?

Suppose so.

EMG

[nageswaragunupudi]

Mr EMG

Suppose so.

It IS so.
I am testing. After some preliminary tests, I can now confirm that in fact it is so.

I get back to you with more details later, but broadly what is happening is this.
After we open a recordset, we are not just doing simple operations and closing the recordset.
We use the object in browses and various other places. As a result we are creating references to this recordset object in various modules and all of them are not going out of scope.
But we are aware of only one variable oRs and even after oRs:Close() and oRs := nil, references to this recset object are remaining in different memory locations in the memory which we are not aware of. Because of this, ActiveConnection of this recordset is not getting closed.

So, for now, it may be safer to close oRs:ActiveConnection also.

I am now examining our libraries and see what we can do the relinquish all references to the object wherever needed

[Enrico Maria Giordano]

NageswaraRao,

So, for now, it may be safer to close oRs:ActiveConnection also.

I'm not so sure. If there is an active reference to oRs somewhere then closing the connection can cause errors elsewhere.

EMG

[nageswaragunupudi]

Mr Rick

Using a single connection and closing the connection while exiting the application surely works well.

But let us continue testing your former approach.
After opening RecordSet you normally use it for browing.
After you exit browse you write
-----
oRs:Close()
oRs := nil
------
Still oBrw:oRs continues to refer to this recordset object. Even after oBrw goes out of scope, the data oRs of oBrw (not any more visible to us) still contains the reference.

So, after browsing please do this:
===============
After browsing in a dialog:

ACTIVATE DIALOG
oBrw:oRs := nil
oRs:Close()
oRs := nil

If you are browing in an MDICHILD:

oWnd:bPostEnd := { || oBrw:oRs := nil, oRs:Close(), oRs := nil }
================
Now surely the ActiveConnection of the oRs also is closed.
I have tested this before posting here.

I am modifying the xbrowse's Destroy() method to set oRs := nil. From the next release onwards xbrowse does not create any problem. Modifying the TDataRow class also. So, FWH library will not stand in your way to achieve closure of ActiveConnection when you close recset.

[Enrico Maria Giordano]

NageswaraRao,

So, after browsing please do this:
===============
After browsing in a dialog:

ACTIVATE DIALOG
oBrw:oRs := nil
oRs:Close()
oRs := nil

If you are browing in an MDICHILD:

oWnd:bPostEnd := { || oBrw:oRs := nil, oRs:Close(), oRs := nil }
================
Now surely the ActiveConnection of the oRs also is closed.

Unfortunately, not. As I wrote in another thread, even ACTION clause can detach oRs and keep a reference to it alive.

EMG

[nageswaragunupudi]

Let us continue the discussion in the other thread