Closing an Access Database ( ldb )- compact and repair

[Rick Lipkin]

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]

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.

[James Bott]

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

[nageswaragunupudi]

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.

[James Bott]

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

[nageswaragunupudi]

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.

[Enrico Maria Giordano]

NageswaraRao,

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

[Rick Lipkin]

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]

Rick,

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: Select all  Expand view  RUN

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

[Rick Lipkin]

Enrico

I tried your suggestion with this code :

Code: Select all  Expand view  RUN

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]

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

Code: Select all  Expand view  RUN

#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

[Rick Lipkin]

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: Select all  Expand view  RUN

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]

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

[Marc Vanzegbroeck]

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]

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?