Base: Execute Query and update tables with button?

English
#1

Does libreoffice Base support a query where I can switch items between two databases and then instantly update a list?

IE, I could read a list of potential attendees… and add them to a database of people who actually arrived. All attendees NOT in the database would be in one table, whereas all people who did arrive would simply not be in the table.

So the tables would be populated with queries, I guess: 1 table queries a database of people who are NOT also (everyone is already aware of their unique ID) on the list of people who arrived, another just shows the list of people who arrived. But is there a way to have a button add someone from one database to another and refresh the tables, without using a macro?

#2

I don’t see a way without a macro.

You have two databases. The one, for example, a Calc-file, which is registered in LO by the name “Import” (“Import.odb”), the other an internal database “MyDB.odb”.

If you do this job directly you will copy the table of the Calc-file to “MyDB.odb”. The data could be appended to a table and an automatic generated primary key could be added. Then the data in Calc-file should be deleted after this action.

If you do this job by a button and won’t have all duplicates in “MyDB.odb” the macro should copy the content from “Import.odb” by a query, which should be able to show only the data, which hasn’t be imported before.

I have just created such a thing for copying from Calc and pasting to an internal Firebird database. But it will need special code for the data you will need. So it would be better you will show a little example here.

#3

Interactively without any macro you can copy the source table into the target database as a temporary import table and write a view selectíng the items from the temporary import that do not exist in the target table. Mind the correct column order.

  1. DELETE FROM TMPIMP
    where TMPIMP is the temporary import table. In the GUI you can select all rows (click the grey box at the crossing of row selectors and column labels) followed by right-click>Remove.
  2. Copy from the source database and paste to TMPIMP. Append records to the blank table.
  3. Copy the view which selects the missing data and paste it on the target table icon. Append records.
  4. Run an UPDATE statement on your target table reading values from the temp. table that do exist in the target table. It is easy enough to store the statement a form document. It will work as long as the view definition and the table definitions remain unchanged.
#4

A Basic macro to execute SQL stateents from a push button form control. Paste the semicolon separated statements into the “additional info” box of the push button and assign the macro to the button’s execute event.
The macro prompts a Msgbox before and after execution. Comment out what you don’t want.

Sub RunSQLButton(e)
Const cMaxLen = 1000
Const cTitle = "Command "
oModel = e.Source.Model
frm = oModel.getParent()
oCon = frm.ActiveConnection
aTags() = split(oModel.Tag, ";")
n = uBound(aTags)
for i = 0 to n
	s = aTags(i)
	sMsg = s
	if len(s) > cMaxLen then sMsg = Left(s, cMaxLen) & cHR(10) &" [...]"
	if len(s)>0 then
		x = Msgbox(sMsg, 35, cTitle &  i +1 &"/"& n +1 )
		if x = 2 then exit sub 'Cancel
		if x = 6 then'Yes
			oStmt = oCon.prepareStatement(s)
			on error goto errMsg
				r = oStmt.executeUpdate()
			Msgbox r &" records affected", 64, cTitle
		endif
	endif
next
exit sub
errMsg:
error(err)
End Sub
#5

Can something like this made to work in writer, rather than basic?

My user base is barely computer literate, and I don’t think teaching them to click through base is an acceptable solution (Only non-technical staff is in a position to operate this.)

I’m not sure, but I’m guessing e.Source.Model.getParent() returns the form? I was going to follow these instructions https://thefrugalcomputerguy.com/grouppg.php?ser=15&grp=21 but it says "Macros will not work in standone form.

#7

That is partly why I stay away from those videos. Macros can work in standalone forms.

#8

Just do not store macros that are supposed to be callable from stand-alone documents within a database document. There are various ways to access databases from stand-alone documents. The above code snippet uses the ActiveConnection of the button’s logical form. The spreadsheet macro connects to a datasource specified by a named cell’s string.
I agree with @Ratslinger that videos toturials are bad.

#9

Hello,
It seems your question(s) do not provide enough information.
.
In a previous question you mentioned people not showing up on various dates - 200 dates seem to have been noted.
.
Does this mean there is a fixed list of people? Doesn’t seem so as this question deals with those not as potential attendees. Does the list grow/shrink or even start over each date?
.
Some further clarity on this entire process may provide a much simpler solution. I can see possibly only needing queries feeding into a report to produce the list wanted.

#10

Yes, the list fluctuates all the time. I even need a way to add people who were NOT on the list.

#6

Create a Writer doc with a form linked to the correct data source.
Compose the relevant DELETE, INSERT, UPDATE statements and test them thoroughly with some dummy data.
Add the Basic macro to the Writer document.
Add a push button to the Writer document.
Add the sequence of SQL statements to the push button (property “Additional info”).
Assign the push button’s execute event to the embedded macro.

EDIT: This would still require that the user is able to copy from one database to another database.
See also Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsheet (View topic) • Apache OpenOffice Community Forum
Link a database range to the relevant data of the source database.
Create the additional headers and named cells.
Add push buttons.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.