[Solved] Macro to Append Data to Database

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
calc-learner
Posts: 40
Joined: Fri Oct 10, 2014 10:14 am

[Solved] Macro to Append Data to Database

Post by calc-learner »

I'm totally new to macros and somewhat new to databases using Base, so please excuse my ignorance here:

1. Is it possible to create a macro to append data from a sheet to a table in the database?
2. Suppose the data columns were something simple like "Key", "Date", "Name" and the sheet was named "sheet1". What would be the macro to insert this into database titled "dbase" and table titled "dtable"? (Assuming the table has already been created already with the exact same columns and that the database is an .odb file found at root directory "C:\". The Datasource URL is "hsqldb:hsql://localhost/;default_schema=true;get_column_name=false". The JDBC driver class is "org.hsqldb.jdbcDriver".)
3. This last question relates to databases and permissions. Assuming all of the above is true about the database, how can I have a remote computer have permission to copy changes to tables (append or otherwise) to the database? I.e. if another user accesses the database and presses F4 and drags data into "Tables" in the database(currently this setup is only allowing the administrator to append to the database this way).

Big thanks ahead of time!
Last edited by calc-learner on Mon Apr 27, 2015 5:37 am, edited 3 times in total.
Apache OpenOffice 4.1.2 Windows 10 Enterprise
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to Append Data to Database

Post by Villeroy »

Everything is possible but a general solution is incredibly difficult because a spreadsheet has only 2 data types which need to be mapped to a dozend of field types in a database. You can easily attach input forms to your spreadsheet and connect them to your registered Base document. A form on a Calc sheet works exactly like a form on an embedded Writer document.
Permissions on database objects are handled by your HSQL database engine. This is documented in subdirectory ./doc/guide/accesscontrol-chapt.html of your hsql package. It is very much identical to other database engines system of privileges with users and groups of users (CREATE USER, CREATE GROUP, GRANT, REVOKE).
After you've set this up, you call Base menu:Edit>Database>Properties..., check the "password required" option and save the database document. Now Base will prompt for user name and password before establishing the database connection.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
calc-learner
Posts: 40
Joined: Fri Oct 10, 2014 10:14 am

Re: Macro to Append Data to Database

Post by calc-learner »

So your suggestion would be create a form inserted in the spreadsheet to handle the data entry? Interesting idea. That just may do the trick.
Apache OpenOffice 4.1.2 Windows 10 Enterprise
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SOLVED: Macro to Append Data to Database

Post by Villeroy »

Yes, forms are not limited to the embedded forms in a Base document which are essentially embedded Writer documents with forms. Stand alone Writer documents, Calc and Draw can hold the same input forms as well. Get toolbar "Form Design", hit the 5th button and insert a new logical form to the current sheet's collection of forms. Build more forms and subforms as needed and add form controls from the "Form Controls" toolbar. This forum's tutorial section has detailed instructions.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
calc-learner
Posts: 40
Joined: Fri Oct 10, 2014 10:14 am

Re: Macro to Append Data to Database

Post by calc-learner »

Thank you for your input, Villeroy. Had a chance to try out what you were suggesting, Villeroy, and I'm still stuck on this.

Here is what I did so far:

1. Made a text box using the form design tool.
2. Went to the "Control" for this text box and went to tab "Data". Here there are two places you edit. I can either link to a cell in the spreadsheet OR link to a Data Field.

But what I need is to have the results of a cell be entered into the data field.

For example, If I made a spreadsheet that calculated results to cells B1, B2, and B3, how can I have these results appended to column 1, column 2, and column 3 in the database with the click of a button?

So it seems I'm halfway there, or maybe there's a part I'm just missing.
Apache OpenOffice 4.1.2 Windows 10 Enterprise
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to Append Data to Database

Post by Villeroy »

View>Toolbars>"Form Design", button #5 calls the "Form Navigator".
Edit the parent form of your form control. You can also right-click>Form... to get the properties of a control's parent form. Specify the source name which points to your registered Base document and specify the table you want to edit. You may want to set the "append data only" property.
The form is a container for form controls and for subforms.
An editable form is bound to an editable record set which consists of one table or some query based on one table. A hierarchy of forms and subforms lets you edit related data across tables. There are plenty of form tutorials in this forum's tutorial section. All the tutorials apply to Calc forms as well except for the layout and formatting stuff. You may have to fiddle around a little bit to get a good looking form.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
calc-learner
Posts: 40
Joined: Fri Oct 10, 2014 10:14 am

Re: Macro to Append Data to Database

Post by calc-learner »

Thank you for that information.

Seems I'm still running into the same issue. I can either select a Data Field OR a Linked Cell and not transfer the information into the Data Field.

Maybe it's necessary to create a macro from the push button that would append the data?

I've attached a very simple example of where I'm at with this.
Attachments
example.ods
(9.73 KiB) Downloaded 561 times
Apache OpenOffice 4.1.2 Windows 10 Enterprise
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to Append Data to Database

Post by Villeroy »

Keyboard --> Form controls --> Database
Database --> Queries --> Office documents (reports, forms, serial letters, spreadsheets)

BUT NOT
Office document --> Form controls --> ...
If you can edit sheet cells you can edit form controls as well.

If you want to mass edit database records of your MySQL database, you better use MySQL tools or give a try to Base's clipboard capabilities.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
calc-learner
Posts: 40
Joined: Fri Oct 10, 2014 10:14 am

Re: Macro to Append Data to Database

Post by calc-learner »

Villeroy wrote:Keyboard --> Form controls --> Database
Database --> Queries --> Office documents (reports, forms, serial letters, spreadsheets)

BUT NOT
Office document --> Form controls --> ...
If you can edit sheet cells you can edit form controls as well.

If you want to mass edit database records of your MySQL database, you better use MySQL tools or give a try to Base's clipboard capabilities.
I'm unclear, Villeroy, on what you mean by "Keyboard" vs. "Office document". Please elaborate.
Apache OpenOffice 4.1.2 Windows 10 Enterprise
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to Append Data to Database

Post by Villeroy »

If you want to get document data into a database without hacking them into form controls, you need some special macro program.
You don't need any macro at all when you use input forms.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply