Closing an Access Database ( ldb )- compact and repair

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Closing an Access Database ( ldb )- compact and repair
Posted: Tue Jul 30, 2013 09:44 PM

To All

I have written a Compact and Repair Access database routine under program control however even though I have no active tables, recordsets, or connections open .. I can not seem to close the .Mdb Database and have Access remove the .Ldb. ( at this point I am the only user .. no other connections or network users present )

I do notice that when my program Quits, all resources, files and databases are closed and the Access .Ldb is removed.

Is there a way under program control to release all Connections to an Access database so I can close the .Ldb ?

Again, my program does not rely on any active connections but I pass the Ado connection string to open a recordset each time I wish to open a table.

I would be most grateful If anyone knows how I can truly CLOSE a database ( under program control ) and make the .Ldb go away so I can run my Compact and Repair routine?

Thanks
Rick Lipkin

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Tue Jul 30, 2013 10:47 PM

Mr Rick

If I remember right, you always open recordset with connection string each time.
This is what I observed from your postings.

That means if you open 5 record sets you have opened 5 independent connections to the MDB.

When you close the recordset, you call oRs:Close(), but never close the connection explicitly. That means the connection is still active and open even after you closed recordset. That in turn means if you opened the same recordset 10 times and closed 10 times, there are still 10 independent active -connections open.

If you adopt the approach of opening recordsets with connection strings, when u close the recordset, please
oRs:Close()
oRs:ActiveConnection:Close()

OR

Open connection once at the beginning, use the same connection object for all recordsets and finally close the connection object.

Even after that wait for about 100 secs or so before assuming that you are totally detached from the mdb.

Regards

G. N. Rao.

Hyderabad, India

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Tue Jul 30, 2013 11:28 PM

Even after that wait for about 100 secs or so before assuming that you are totally detached from the mdb.

I assume this is to wait for the write cache to be written to disk. I know the COMMIT command is for DBF's but I wonder if it just forces the write cache for the entire app to be written? Maybe try it.

Regards,
James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 12:15 AM

James Bott wrote:
Even after that wait for about 100 secs or so before assuming that you are totally detached from the mdb.

I assume this is to wait for the write cache to be written to disk. I know the COMMIT command is for DBF's but I wonder if it just forces the write cache for the entire app to be written? Maybe try it.

Regards,
James

I advised it as matter of caution.
In another posting, one of colleagues complained about similar issue regarding SqLite database. In that case, on the advice of EMG, we tried delay of 100 seconds and it worked.

Regards

G. N. Rao.

Hyderabad, India

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 12:18 AM

Here is some info by Microsoft:

How to shut down a custom Access application remotely
http://support.microsoft.com/kb/304408

The sample code is in VB but these are the commands I think you need:

Application.Quit acQuitSaveAll

I don't know how to translate them into ADO.

Regards,
James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 12:27 AM

That is not ADO.
That is a code inside Access Forms, to close the application. I mean the Access Aplication.

In ADO we never open the Access Application.

Regards

G. N. Rao.

Hyderabad, India

Enrico Maria Giordano

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 08:09 AM

NageswaraRao,

nageswaragunupudi wrote:When you close the recordset, you call oRs:Close(), but never close the connection explicitly. That means the connection is still active and open even after you closed recordset.

As far as I know, oRs:Close() closes the related connection too.

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 01:15 PM

Rao

If you adopt the approach of opening recordsets with connection strings, when u close the recordset, please
oRs:Close()
oRs:ActiveConnection:Close()

I tested your above suggestion .. when I open my applications I open a security table ( just once ) and the .ldb was created .. I added the ActiveConnection:CLose() after the recordset close() and INDEED the .ldb went away.

Now I am at a crossroads .. asking myself, what harm is there in leaving a ( single ) active connection open which is no different than opening a connection and passing it to each recordset as I need it ?

My only concern ( as Rao mentions ) .. everytime I open a recordset does the number of connections continue to add up ?? or is a connection a ( single ) connection and once you open a recordset does the active connections keep increasing or is the same connection recycled with the next open recordset ?

I am going to test my concerns shortly .. the only advantage in creating the ( on-demand ) connection ( just passing the connection 'string' ) each time I open a recordset is that I do not have to keep an active oCn and count on it to be active at any one time in my application ( potentially ) giving me an un-welcomed run-time surprise at any point when I open a new recordset.

Don't know if there is any right or wrong answer here .. my only concern is what if any ramifications there are if the open connections continue to add up each time I open a new recordset and what harm ( pragmatically if any ) does that cause... or perhaps a licensing issue with the number of active connections if active connections are counted as concurrent licenses. In all my years in State government I never had a licensing issue with Sql Server with any of my applications.

Rick Lipkin

Enrico Maria Giordano

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 01:28 PM

Rick,

Rick Lipkin wrote:I added the ActiveConnection:CLose() after the recordset close() and INDEED the .ldb went away.

I think the problem is the ownership of oRs variable. Try with a straghtforward code like this:

Code (fw): Select all Collapse

oRs = CREATEOBJECT( "ADODB.Recordset" )

oRs:Open( ... )

...

oRs:Close()

You will see that LDB file is automatically closed and deleted. If you are using a browse, it is probably the browse itself that keeps the ownership of oRs variable preventing it to properly close.

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 02:04 PM

Enrico

I tried your suggestion with this code :

Code (fw): Select all Collapse

oRsUser  :=  CREATEOBJECT( "ADODB.Recordset" )

*oRsUser := TOleAuto():New( "ADODB.Recordset" )
*oRsUser:CursorType     := 1        // opendkeyset
*oRsUser:CursorLocation := 3        // local cache
*oRsUser:LockType       := 3        // lockoportunistic

cSQL := "SELECT * From [Staff] Order by [Lname]"

TRY
  oRsUser:Open(cSQL,xConnect, 1, 3  ) 
CATCH oErr
  MsgInfo( "Error in Opening Staff table" )
  RETURN(.F.)
END TRY

...
...

oRsUser:CLose()

The .ldb was created .. but after the close() the .ldb remained

.. Do you see anything in the above code you would change ? .. xConnect is just the connection string.

Thanks
Rick

Enrico Maria Giordano

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 02:54 PM

Ok, as I said, the problem is the variable oRs ownership. Try the sample below:

Code (fw): Select all Collapse

#define adOpenForwardOnly 0
#define adOpenKeyset      1
#define adOpenDynamic     2
#define adOpenStatic      3

#define adLockReadOnly        1
#define adLockPessimistic     2
#define adLockOptimistic      3
#define adLockBatchOptimistic 4

#define adUseNone   1
#define adUseServer 2
#define adUseClient 3


FUNCTION MAIN()

    TEST()

    INKEY( 0 )

    RETURN NIL


STATIC FUNCTION TEST()

    LOCAL oRs := CREATEOBJECT( "ADODB.Recordset" )

    oRs:CursorLocation = adUseClient

    oRs:Open( "SELECT * FROM Clienti ORDER BY Cliente", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=clienti.mdb", adOpenForwardOnly, adLockReadOnly )

    WHILE !oRs:EOF
        ? oRs:Fields( "Cliente" ):Value
        oRs:MoveNext()
    ENDDO

    oRs:Close()

    RETURN NIL

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 03:24 PM

Enrico

Unfortunately modifying my code like yours had no effect on closing the .Ldb. The only difference in my connection string is that I have a password on my .mdb.

Here is my code based on your suggestion.

Rick Lipkin

Code (fw): Select all Collapse

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE   := cDEFA+"\Groom.mdb"
xPASSWORD := "xxxxxxxx"

xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD

oRsUser  :=  CREATEOBJECT( "ADODB.Recordset" )

*oRsUser := TOleAuto():New( "ADODB.Recordset" )
*oRsUser:CursorType     := 1        // opendkeyset
oRsUser:CursorLocation := 3        // local cache
*oRsUser:LockType       := 3        // lockoportunistic

cSQL := "SELECT * From [Staff] Order by [Lname]"

TRY
  oRsUser:Open(cSQL,xConnect, 0, 1  )
CATCH oErr
  MsgInfo( "Error in Opening Staff table" )
  RETURN(.F.)
END TRY

msginfo( "Check ldb" )
oRsUser:CLose()
SysReFresh()
Msginfo( "Check Closed" )

Return(.f.)

Enrico Maria Giordano

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 03:31 PM

Rick,

You didn't get the point. You must test the LDB when oRs has gone out of scope. Please, test my exact sample changing only the connection string and the query.

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 04:34 PM

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

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Closing an Access Database ( ldb )- compact and repair
Posted: Wed Jul 31, 2013 04:38 PM

Enrico Maria Giordano wrote:Rick,

You didn't get the point. You must test the LDB when oRs has gone out of scope. Please, test my exact sample changing only the connection string and the query.

EMG

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?

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums