Ask Your Question
0

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

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

jay Arr gravatar image

updated 2020-07-12 20:31:32 +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 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

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 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
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

Question Tools

1 follower

Stats

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

Seen: 1,634 times

Last updated: Feb 14 '17