I make a copy of my LibreOffice Base database, and my mysql database at the end of the financial year, and rename them, then continue to add data. This allows me to have multiple years on-line at the same time. The only problem is having to manually change the database name in every base form from last years mysql database name. Is there a global find and replace option?
consider pointing to a dummy MySql database that is just a view onto whatever live database you choose. The names would not need to change in LibreOffice – the name of the view would remain the same. The view would provide an easy centralized way to change the references.,
Just realised that using queries instead of views to display tables within LO Forms avoids having the MySQL database name embedded in LO Forms. This should fix my annual portability issue.
@davdaw1: I think you answered your own question. Its all in the design of a query. Post your answer.
I would suggest just having one mysql database. There is no need for multiple mysql datasets. Since you have financial records, date data should be available for designing queries based on a fiscal year. Another option, have (add) a field specific to the fiscal year for each transaction, but that should no really be necessary.
Partial solution has been to replace all Views in Forms with Queries, since the MySQL Database name is not stored in the LO Base Query, like it is in Views, which are part of the MySQL database. This leaves about 35 Tables in the database referenced in over 30 Forms to change. I wanted to preserve a copy of both the LO Base App and the MySQL DB as at the end of Financial Year so I can continue to refine and develop both the LO Base App and the MySQL database. Thanks for all the suggestions for resolving the issue.