# macro for a clean database ending and exit from LibreOffice [closed]

I'm developing an ongoing split database application with OSX 10.10.5 with LO Version: 4.4.6.3

I want to give my users a clean exit from the application AND importantly from LO. Currently I'm using this macro actioned from a push button on switchboard form:

public sub sbTerminate
dim nf          As Integer
dim oCollection        As Object
dim StarDesktop as object

'close all open forms
Set oCollection = Application.Forms()
For nf = 0 To oCollection.Count-1
Application.Forms(nf).mclose()
Next nf
' close the database
call DBClose
' now close the application
Application.Quit

end sub


However I see this ABORT message when it runs

Error #1501 (No active connection to a database found) occurred in a call to function 'Quit'

My question is what do I need to do to my macro to achieve my objective?

N.B. I would prefer NOT to have to kill any LO process by shelling out to the underlying system since the application is designed to be platform independent.

I have tried the macro without the call DbClose statement BUT I cannot guarantee that the application does a clean re-startup every time. Sometimes it does and sometimes it has to recover the database, which is an unnecessary worry for my mainly non-IT users.

=================================================================================================

Thanks JPL to answer your questions DBCLOSE is a routine to close a SPLIT database

I've taken your advice and experimented with my routine and it now looks like this

public sub sbTerminate
' terminate the database gracefully
dim nf              As Integer
dim i               as integer
dim fnames()        as string
dim oCollection     As Object
dim closingMsg      as string

' set up error trapping & call stack tracking
on error goto sbTerminate_Error
call acbPushStack("sbTerminate")

' close all recordsets
closingMsg="closing the recordsets"
Application.Currentdb().CloseAllRecordsets()

' close all OPEN forms
closingMsg="closing the open forms"
Set oCollection = Application.Forms()
nf=oCollection.Count
if nf<>0 then
' get the names
' n.b.looping through Application.Forms(i).mclose()
' DOES NOT work if there is  more than 1 form
' because the count & index references change
' each time Application.Forms is called within the loop
' so have to reference forms by NAME to close.
redim fnames(nf-1)
For i = 0 To nf-1
fnames(i)=Application.Forms(i).Name
Next i
' close all forms
for i=0 to nf-1
Application.Forms(fnames(i)).mclose()
next i
endif

' shutdown & compact the database
closingMsg="shutting down & compacting the database"
DoCmd.RunSQL("SHUTDOWN COMPACT")

' disabled closing the database connection
' becase this gives problems with Application.Quit
' closingMsg="closing the database connection"
'   call DBClose

' finally quit the application
sbTerminate_Exit:
call acbPopStack
call QuitApplication
exit sub

sbTerminate_Error:
call ErrorReport("Problem with " & closingMsg)
resume sbTerminate_Exit:

end sub

sub QuitApplication
' currently this quits the Base
' application it does  NOT
' shutdown LibreOffice
Application.Quit
end sub


To explain: the acb... calls are to my stack tracing & error logging routines. I've deliberately separated out the problem of terminating LibreOffice into another routine because I've realised want to do ...

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-09-24 15:19:21.674457

Sort by » oldest newest most voted

Hi

1) When you want to change a collection that you goes through, you can use step -1 instruction. E.g.

for i = ThisDatabaseDocument.FormDocuments.count - 1 to 0 step -1
ThisDatabaseDocument.FormDocuments.getByIndex(i).close
next i


However, this does not seem necessary. The following code closes "properly" the database and all opened forms:

ThisDatabaseDocument.Close(True)


2) Since version 3.6 the software automatically compact bases. SHUTDOWN COMPACT is no more useful and it effects a disconnection from the data source unlike CHECKPOINT DEFRAG.

3) I am not in favor of closing LibreOffice from a database: the user can open other documents (writer, calc ...). Whatever IMHO the quit should be made from a LibreOffice module and not from the base.

HTH Regards

more

Hello So, after that long discussion, is there a usable macro on hand, to trigger from a button in a Base Form, a clean exit for DB and LibO(V5) ? I'll take it ! Bob

more

Regarding your (3) point in general I would agree with you.

However this routine is part of a large integrated application built using LO's database & writer facilities (see this entry)(https://ask.libreoffice.org/en/question/63741/public-database-example-of-application-as-a-learning-resource/) and as such the users do not need have ( and I don't want them to have) knowledge of the underlying engine. This being the case it's not unreasonable for them to have and use a "quit application" button and for it to function like [cmd-Q].

[SOAP BOX WARNING] My impression FWTW, is that I don't think the developers have envisaged LO being used in this way, certainly not for a database application. The basic application building tools and functions which can be found in MS-Access for example; like being able to start up without the GUI, having a clean shut down & exit, being able to protect/hide code, properly integrated code IDE, conditional compilation, identical operation across all platforms, etc. all appear to be missing. [END OF WARNING]

more

What is the code behind ?

call DBClose


If it contains a CloseConnection method, then it should be left out.

I don't know why sometimes the database has to be recovered. Have you setup the database in the so-called "split" mode ? What you can try also: - ensure that all recordsets are closed (CloseAllrecordsets method) - Force a clean shutdown of the database: something like

DoCmd.RunSQL("SHUTDOWN COMPACT")

more