I am new to libreoffice base, but worked with MsAccess. I was used to store data in one .mdb (for libreoffice .odb) en create from, reports etc in another mob. Is it possible in Libreoffice to connect one .odb with data to another with de reports and forms.
There is no easy way for this to be done and don’t know what benefit there is to doing such a thing. If you wanted to slow down processing and increase development ten fold you can, with macro coding connect from one .odb to obtain data from another. Therefore, it would seem possible to do what you ask using an extreme amount of code.
If this answers your question please click on the (upper left area of answer).
Thank you for this answer.
The benefit is you keep data and programs separated. You can change en fiddle around with your macros etc without destroing your production dat. That is what is very common in the good old mainframe area. It is also very easy to be done in the MsAccess database environment. So why not in the Libreoffice environment. You can connect to MySQL etc, that is the same, programs in one environment and data in the other, but not to ODB, that’s strange.
I don’t understand much of your comments. Base is not a database. Base comes with embedded HSQLDB for the database. You can use whatever DB you wish to contain data - MySQL, PostgreSQL, MariaDB, Firebird, and more. Even connect to Access .mdb. This doesn’t mean you use two .odb files. For testing, whether mainframe, desktop or server you never use production files - always a copy. Have used Access a lot in past, but never for how you have stated.
I have used Access a lot in the past and have always kept the Tables in an .mdb file and the front end in another .mdb file. You can link to the Tables in the ,mdb containing them in the front end .mdb.
@peterwt Thank you for the comment. To be clear, it is not that I didn’t know that the function is in Access. I just don’t choose that method. The whys of either side ends up in a long discussion which is not for this forum. I do concede, however, that there are certain capabilities lacking in Base regarding access to tables in other databases.
Thank you. I did not know Base was NOT a database. As said I am trying to migrate away from Windows and MS access and thought Base was a good replacement. So I have to figure out something else.
What I stated is true. Base is not a database but rather a front end to a database. It does come with a database - embedded HSQLDB but you can use (as mentioned) others. LO Base would be the typical alternative to Access.
Both @Ratslinger & @peterwt are correct. Base is a front end (& Access can also be so regarded) but in ‘simple’ use Base is connected to the embedded HSQLDB & the resultant DBMS is often referred to simply as “Base”. Access also uses an embedded structure in ‘simple’ use but splitting it into two .mdb files one (front end) referencing the other (back end) has the advantages @linuxjoeser describes & desires. @peterwt’s link has links & onlinks that explain this for “Base” & how to achieve it.
The default setting for Base is the embedded database where everything is in the single .odb file. You can create a split database where the database is stored in a separate file. The database file is not a .odb file but one containing the HSQLDB in a file. See here
Thank, I’m going to try this, or the MySQL solution. I think the later is more convenient for me.
Thank you all for the explanations and patience.
I had used Access for many years, but am also migrating away from it.
I looked around and could not find anything better that LibreOffice Base. If by chance you find something else, please suggest it back here as I would find it interesting.
Given that databases are much more complex than many other data structures, migrating from any database to another database is going to take some re-thinking and quite a bit of work.
I have found Base able to do most things Access could do, but at times I had to re-think how to design things. Base also has a few shortcomings relative to Access, but Access also has a few shortcomings relative to Base. I remain hopeful that as time goes on some of these issues will get improved. If you want to directly replace Access, Base won’t do that. If you want to build a system that pretty much does what your Access did, then you probably can accomplish that.
One thing I like about Base is that it has a good group of people with a fairly long history of self-support, like in this web site for example. Also LibreOffice is very similar to OpenOffice and so you have multiple places to search for things which sometimes helps.
Most of time I “connect” Base to MariaDB (a MySQL clone). This gives the split advantages you cite, and it also gives me other standard tools like HeidiSQL for example, to adjust my data.
I can’t know from your question how big or involved the data is that you wish to migrate. But I have pulled in hundreds of tables into Base and am now working on rebuilding my data user interface.
One other thing you might want to consider is the migration itself. As for me first I copied my data out of Access and into MariaDB, then I reconnected Access to it with links. That made it so I could continue to use my Access front end as development proceeded in LibreOffice. In my case I setup a dual boot system, but you could as well set up a local or remote database server. I also had the challenge of moving from Windows to Linux, and there are a few things I needed to do to my data, like adjusting identifiers and the like. Base’s report writer is somewhat more simple that Accesses. You definitely need to play with it before starting your project to make sure it will do what you want. I plan on remaining to use some of my Access reports in Windows because they just work so much better, but doing my data entry in Linux/LibreOffice/Base.
I would recommend you not migrate whole hog at first, but rather just start to play with Base and get to know how it works and what it can do, and then as you have questions interact here.
Finally, here is a documentation guide I compiled as I was first learning this stuff which I still maintain.