Ask Your Question
0

macro for a clean database ending and exit from LibreOffice

asked 2016-02-04 15:45:08 +0200

jay Arr gravatar image

updated 2016-03-09 21:26:06 +0200

Alex Kemp gravatar image

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 ... (more)

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
0

answered 2016-02-07 11:00:15 +0200

pierre-yves samyn gravatar image

Hi

Some comments...

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

edit flag offensive delete link more
0

answered 2016-02-06 10:29:23 +0200

JPL gravatar image

updated 2016-02-06 10:37:50 +0200

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")
edit flag offensive delete link more
0

answered 2017-02-14 10:14:56 +0200

kirsch gravatar image

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

edit flag offensive delete link more
0

answered 2016-02-07 12:12:34 +0200

jay Arr gravatar image

pierre-yves samyn thanks for your comments. I'll modify my code accordingly.

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]

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-02-04 15:45:08 +0200

Seen: 1,093 times

Last updated: Feb 14 '17