![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Hello,
I'm new user of LO Base, we are transitioning from MS Access, and I need to
redesign our db from it. The base contains some VBA macros.
I have read the Book LO Base, and made some tables and forms.
I have some difficulties with macro creation.
What I need macro to do?
The base is warehouse management tool. I need form that has 3 drop down
lists
1) Product group select,
2) Product sub group select (Filter those by group)
3) Product select (Filter by subgroup)
And one field for insertion of number or items that has to be added/removed
In form then need add /remove Amount (number inserted in form) of selected
product from warehouse.
I have difficulties with accessing the form data. And proper database
connection with macro.
Thank you so much.
Best,
Milica
Hi 
At the bottom of the programmers section
https://wiki.documentfoundation.org/Documentation/Other_Documentation_and_Resources#Programmers
is Andrew Pitonyak's guide to macro programming.
Sorry no-one has responded earlier! Hopefully someone might be able to
give a better answer sometime soon(ish) but it's worth checking Andrew's
guide
Regards from
Tom
Thanks Tom, I'm reading his book now.
I'm new user of LO Base, we are transitioning from MS Access, and I
need to redesign our db from it.
One word of advice based on experience:
Avoid use of the "built in" database.
Use a proper client-server RDBMS. Preferrably PostgreSQL, as it's
reliable and the driver comes with LO.
Otherwise you *will* regret it the day your data has gone to Nirvana.
Sincerely,
Wolfgang
Hi
Good point!!
An external database is a really good approach. Many people go for MySql
or MariaDb (unless using a Mac) but that's owned by Oracle and some worry
about the future of "free" or "Open Source" under Oracle. Many go for
faster, lighter java back-ends such as Hsqldb as an external but others
worry about the whole java issue.
So Postgresql might well be a good choice. They have put a lot of work
into building a decent connector for Base.
Regards from
Tom
Thanks,
Actually, that base in ms access is kind of start point, and needs to be
more developed,so I have to learn LO macros anyway 
I'm reading the book OpenOffice.org Macros Explained (great book!), and
making new version in LO but in LO Calc.
Thank you once again :))
Hallo Wolfgang, hallo LO-users
may I ask a question in line with that thread:
a few month ago, I failed installing LO-base with PostgreSQL-DB when I
stumbled over the input request
URL of data source ?
Help will not help.
I suppose something like
sdbc:postgresql:dbfile
is wanted, however
missing "=" after "test" in connection string
What format should that URL have?
Some examples of formally correct URL would help.
What is a possible correct driver name?
Thank you for your help
Walther
Debian wheezy/PostgreSQL paket installed. LO 4.1.3.2
Tom, If you read your posting again, it implies that MariaDb is "owned by Oracle". That would be news to me and, I suspect, the MariaDb development team.
Girvin Herr
I have not been tracking MS Access system design since version 1.1, but back then Access did have an external database back-end (server) engine called Jet. Jet was bundled in with Access, much like Base uses HSQLDB, but I think it could be used by other front-end clients than Access. So, the MS Access client-server topology was not that unlike Base. Base just allows one to get out of the MS Access proprietary "silo" and allow much more control of the database system.
For the record, I am using MySQL with Base, soon to be switching to MariaDB. I have not needed to use any macros in my work with Base forms or reports. I have made it a point to avoid macros because they are very client-dependent and they would lock me in to a specific client, much as the MS Access macros in your database are causing you problems now. I have been able to do some of what you are needing to do with forms, only I used queries. Many of my forms have list boxes where I select one of a list of options to be inserted into a database field. Although I have not used it, there is another type of drop-down listbox that presents options, but if an option not in the list is needed, the user may enter that option into the field. I may have misread your posting, but that may be what you need. I can see that there are times when macros would be the only answer to a problem, such as with custom forms, but my recommendation is to avoid them whenever you can.
Girvin Herr
+100 Wolfgang!!!
I fell in the same hole!!! PostgreSQL or MySQL is the ONLY way to go.
IanW
Hi
Dohh! MariaDb is definitely not owned by Oracle! They forked away from
Oracle's brand just as LO did and for much the same reasons.
Regards from
Tom
Dear Girvin,
I have that base that we use in our workshop like warehouse
management,(select product, type amount that needs to be added/removed from
warehouse and macro does it) and also base needs to create work orders
based on calculation (for every product, how many half products there is to
be made) times number of products needs to be made,and then write that on
some report.
I pretty mush did all the work in LO Calc, and the SQL is too much for me
currently. And I do that for personal use, since we have to switch from
XP/Access to Ubutu/LO on our workshop computer.
I cannot ask you to do that work for me, and I'm not sure If I could handle
that myself.
Thanks,
Milica
Hi
Can you post some of the old macros as plain text and give a rough idea of
what each does. SQL is usually easier because you get a nice gui to do a
lot of the work in a nice point&click way. Some of the algebraic formulae
might be much the same or perhaps a little less convoluted.
Plus Sql is more generic and less dependant on specific product and
versions. On the other hand the LibreOffice/OpenOffice macro language is
also much less version-specific than MS macros.
I'm not certain that Girvin is offering to do the work for free. Knowing
him he probably is but it might be better if there was a potential for
payment for work done, unless exchange-rates make that unworkable (as often
happens).
Regards from
Tom
IIRC MySql is owned by Oracle, but *MariaDB is not*, so I believe there
should be no problem using MariaDB to connect with Base via the
appropriate connector.
HTH.
john
may I ask a question in line with that thread:
a few month ago, I failed installing LO-base with PostgreSQL-DB when
I stumbled over the input request
URL of data source ?
Since the PostgreSQL SDBC driver uses the native PostgreSQL protocol
(way more efficient than such kludgy crutches as ODBC or JDBC), the
connection string would have to look like this (one line, pairs of
key=value, separated by a space):
dbname=your_database_name hostaddr=127.0.0.1 port=5432
user=your_database_user password=your_password
Here we are connecting to localhost (127.0.0.1).
What format should that URL have?
Some examples of formally correct URL would help.
What is a possible correct driver name?
The PostgreSQL documentation lists all available options for the
connection string:
http://www.postgresql.org/docs/9.3/static/libpq-connect.html
in section 31.1.1. Connection Strings.
I don't have a clue how to connect to a specific schema within a
PostgreSQL database though, LO only seems to be able to connect the
default schema.
Sincerely,
Wolfgang
Perhaps this might help: (chapter 5.7)
http://www.postgresql.org/docs/9.3/static/ddl-schemas.html
--Dan
Tom & Milica,
No! No! No! I am not offering to do the work. I apologize if I somehow implied that. I have zero experience writing LO macros of any sort. I was just suggesting to avoid macros wherever possible. Recreating data entry forms and reports when there is a need to migrate to another client (front end) is enough of a problem without having to re-write macros too.
I use the Base query editor as Tom suggests, which is a nice GUI shell around the SQL, to create my table data relationships, aliases and sorts. It is very similar to what Access 1.1 had to define similar relationships. It works great. If you want to see the underlying SQL, it can switch modes to show the SQL and even test run it to see the resultant output in table form.
I was just suggesting to look at using a query or two rather than macros, wherever possible. Another aspect of this is that a query should run the actions, such as a sort, on the database server (back-end) and should run faster than a macro running in Base. I am curious: Does the Base macro "engine" run in Java? Has anyone tested this probable speed difference?
Girvin
Everything I read about MariaDB states that it is a plug-in replacement for MySQL. I also discovered the MariaDB programs even retain the MySQL names as well as their functionality, such as mysql_install_db, so scripts and procedures should run without change. I am still running MySQL on my Slackware 14.0 system, but when I upgrade to Slackware 14.1, I will be using MariaDB. Slackware has replaced MySQL with MariaDB starting with version 14.1. The compatibility is also reported to include the interface drivers, such as Oracle's mysql-connector-java. So, a MySQL / Base system should be fully compatible with MariaDB with no changes.
HTH.
Girvin Herr
Tom & Milica,
No! No! No! I am not offering to do the work. I apologize if I somehow implied that. I have zero experience writing LO macros of any sort. I was just suggesting to avoid macros wherever possible. Recreating data entry forms and reports when there is a need to migrate to another client (front end) is enough of a problem without having to re-write macros too.I use the Base query editor as Tom suggests, which is a nice GUI shell around the SQL, to create my table data relationships, aliases and sorts. It is very similar to what Access 1.1 had to define similar relationships. It works great. If you want to see the underlying SQL, it can switch modes to show the SQL and even test run it to see the resultant output in table form.
I was just suggesting to look at using a query or two rather than macros, wherever possible. Another aspect of this is that a query should run the actions, such as a sort, on the database server (back-end) and should run faster than a macro running in Base.
Using a proper SQL server + Macro's + dialogs (no forms) but using the dialog controls to visualise the data is not the easyist way but opens a never ending route.
I am curious: Does the Base macro "engine" run in Java? Has anyone tested this probable speed difference?
not the macro's make a difference , you just use a macro visualise the data and to pass the SQL statements to the server who change , update or delivers the data. Only the connection with your server and the data volumes influence the speed.
Hi
Macros are code running on a desktop machine right? So don't they take up
cpu cycles? If the macro code is then running stuff through Sql code then
it's going to take more Cpu cycles than just running Sql alone. So in
terms of network traffic they might not count for much but in terms of
end-user experience it might be critical on low-spec machines.
As for visualisation of data SQL statements built-up through the Base gui
can show the resultant data neatly in a table that looks like a
spreadsheet. I don't think macros can do that.
A proper programmer might be able to visualise data more easily without the
distraction of something being right in front of her/his eyes and thus
obscuring abstractions and specific cases at extreme ends of the possible
data but most of us need to see something 'concrete' before we can begin to
start on that sort of visualisation.
On the other hand if macro language is easier to understand and code for
then that might well be the best route for surest victory right now.
Perhaps look into using a gui to build Sql statements after you have first
got something running using macros. "Release early and release often".
The macro version would be the 1.1 and the non-macro would be the 2.0 with
advantages such as being faster. People could buy into either one without
having to worry about incompatibility issues when trying to communicate
with people using the other. Also learning the LO macro language means you
can write macros to do other things and unlike with MS macros don't have to
worry much about those macros becoming out-dated by the next release of LO
or AOO or AndrOO or whatever.
Regards from
Tom