Saving data continuously

[Dan Robinson]

I work as an academic advisor, and I've created an OpenOffice database for managing information on my students. It's saved on a secure university server, which unfortunately goes down from time to time. Whenever the server goes down, I end up losing all the data I've entered that day, which is a huge problem. (This has happened to me twice now.)

It seems like Base doesn't really save the data I'm entering until I close or save the entire file. (Sometimes, confusingly, the "save" icon isn't even available, although I've clearly created and/or modified records since the last save.)

I want Base to save changes to the database IMMEDIATELY as I make them (i.e., whenever I update a record). That way, I will never lose any data. Is there a way to do this?

Thanks,

Dan

[r4zoli]

It caused by HSQLDB running in memory and files remain open until Quickstarter not closed.
One possible solution:
Open SQL window and insert the command:
Code:
CHECKPOINT DEFRAG
then save the ODB file.

If you're looking for a full reference of SQL commands you can find it in HSQLDB documentation.

[Dan Robinson]

Hmmm... It sounds like you understand the problem, but I'm not sure I understand your solution. I don't know how to code in SQL or anything like that. I'm just an "averge Joe" user, so I'm going to need step-by-step instructions here. Thanks for your help!

[r4zoli]

1. Open your Base Database file
2. From the Menu: Tools -> SQL... -> in the Command to execute area, enter:

Code: Select all

CHECKPOINT DEFRAG

3. Press Execute button

See attached screen shot:

[Dan Robinson]

Well, thanks for the suggestion, but it doesn't seem to be working. I typed the "CHECKPOINT DEFRAG" command as instructed, saved the file, closed it, reopened it, made changes to a few records, then forced the computer to shut down (to simulate a crash). When I turned the computer back on, OpenOffice auto-recovered the file, but all my changes had disappeared, as if I never made them. So, apparently, Base did NOT save the changes each time I updated a record.

FYI, I also tried the whole exercise again but prevented auto-recover from running. I got the same result.

I don't know anything about SQL, but it seems to me that this CHECKPOINT DEFRAG command is kind of a weird, back-door way to approach this problem. I'm not interested in having my database continuously defragmented; I simply want Base to save each change I make to the database immediately as I make it. This is how most other databases I'm familiar with operate. I don't want to be asked at the end of a long session whether to discard or save all the changes I've made to the database during that session; I want each change to be saved as I go along. That's the only way I'll feel safe using OpenOffice to keep track of my data.

Dan

[r4zoli]

Base and built in HSQLDB works this way, if you want to avoid this, use another DB and use base as frontend to them. Use MySQL or any other FOSS database engine.

[TerryE]

Dan, I would call standard Base a document that is accessed using a SQL mechanism rather than a database. Why? because the HSQL D/B is stored inside the ODB container which is only written to disc if you save this. If you don't then all of your changes are lost. This is a very different paradigm to anyone used to working with databases.

As r4zoli says, what you really need is a conventional D/B such as MySQL. However Base does support this in that you can use it in a mode where you link your data tables to an external database. In this case, the ODB file only contains the Base application and some metadata. Your business data is stored in a real D/B. This is what you need for your requirements.

[Anne]

I am also interested in this topic. I have a small db application, and I want the changes that are made to be permanent as they are made. You have suggested the CHECKPOINT DEFRAG command. If I execute this command in my macro code after adding/deleting/changing records, will it save everything done to that point permanently?


Thanks for your assistance.

[QuazzieEvil]

You can programmatically save the db doc the record is changed (on the form). See following code

Code: Select all

Sub saveDBDoc(Event As Object)
	REM BIND TO 'AFTER RECORD ACTION' FORM EVENT
	Dim DBDoc As Object
	
	DBDoc=Event.Source.Parent.Parent.Parent
	DBDoc.store()
End Sub

1) Copy code above to your macros library-->Tools | Macros | Organize Macros | OpenOffice.org Basic.
2) Find the libarry/dialog of choice in the entry labeled My Macros, or create your own
3) open your data entry form in edit mode (right-click and select Edit). open the form properties dialog, and open the Events tab. select After Record Action and follow dialogs to select the macro (from above)--where ever it was saved.


What will this do? it will save the parant databse every time a record is changed (INSERT, DELETE, UPDATE). You must follow these steps for every form.

[Anne]

Many thanks for sharing this code! I decided to use ThisComponent.Parent.store() instead of Event.Source.Parent.Parent.Parent.store(), because then I can use the routine to save the database from anywhere - not just after specific events. Am I correct in assuming this will call the same routine? It seems to work, and, since it is independent of an event, I have been able to call saveDBDoc after running UPDATE statements in my macros, when the application is exiting, etc. Please let me know if you know of any reason why ThisComponent.Parent.store() should not be used. I am indebted to you once again, Quazzie.... Thanks

[Villeroy]

When you enter data into a Base form, everything happens in the memory of your machine whithout submitting anything to disk, but the form becomes "dirty" (having unsaved data). Toolbar "Form Navigation" has buttons to navigate between records. Before you get to another record while having dirty data in the current record, Base tries to write the current record to disk. Then it loads the requested "clean" record from disk into the form. If you close a dirty form, Base asks you if you want to save the content of the current form (not the form itself). Toolbar "Form Navigation" includes also buttons to
- reset the current form (restore the current record from disk, wiping out all dirty data)
- submit the current form (store the current record to disk, making the form "clean" without navigating to another record)
- delete the current record
- other buttons operate on the whole recorset by means of filtering and sorting
If you don't like the built-in navigation tools, you can have equivalent buttons directly on the form, a save-to-disk-button for instance.

Edit: I forgot: You can also draw a full navigation bar onto the form. Toolbar "FormControls", button "More Controls" opens a toolbar with some "exotic" tools.

Screenshot FormDesign, Navigator, Data-Properties

Writer-form openened in edit mode. 2nd button on toolbar "FormDesign" toggles between design mode and working mode. While in edit mode, the 5th button on toolbar "FormDesign" loads the "Form Navigator" showing the Writer document's hierarchy of forms and controls. The dialog shows the data-properties of the currently selected "MainForm".

The form displayed in the database's form container is basically a Writer document with the "real form(s)" attached to it. You can store the Writer document after changing the document itself in edit mode (right-click the form and choose "Edit"). In edit mode you can edit the Writer document and you can edit the layout as well as the inner workings of the embedded "real" form. The form is not functional in this state. However, you can switch the form into working mode by using the second button on toolbar "Form Controls" or "Form Design".

When you simply open(double-click)the Writer document in working mode, the Writer document opens in read-only mode (as indicated in the window title), with the form beeing functional. The first record gets loaded into the form and any change makes the form dirty. There is no way to change the form's layout or other settings.

The form on the screenshot: http://user.services.openoffice.org/en/ ... hp?id=1266