Windows to Linux LOB db interchangeability?

If I Import an MS Access 2007 DB into LibreOffice Base in Windows, can the new db be used in a Linux installation of LibreOffice Base then?

Since LibreOffice Base for Linux can not import MS Access databases directly, but the Windows version can, this could save me.

You do not seem to understand Base. Base is NOT a database. It is a front end to databases. It connects to databases through connectors, some built in and some external. It can, through these connectors, access databases such as HSQLDB (embedded comes with LO), Postgresql, MySQL, MariaDB and many others including Access. When you connect to these databases, the data stays where accessed and is not brought into Base.

Therefore, whether using Windows or Linux or OSX you still need a connector to the data.

Edit: Also it must be noted, only table data is available within base. Any forms that are in Access are not compatible. All forms must be redone in Base. If there are any macros in Access, these too may not be entirely usable within Base.

Edit 4/7/17:

@Edward2 Not positive but I believe the connector is only available in Windows because it may use some Windows OS components for the connection.

You will always have multiple files when dealing with Base (and many other products). The Base .odt file alone has multiple files zipped and then the actual data location even if you cannot see it readily (Base embedded HSQL data is in the zipped .odb).

First, here is the LO documentation site (all products) - click here. Take a look at Chapter 2 for external & other DB connections.

Here is a method to create a split DB (much better than embedded) - link text.

Here is a post about external DB’d & connectors - click here.

Use UCanAccess JDBC connector mentioned in first post to get connection to Access tables in Linux. I use it all the time on many distros including Mint 18.

Transferring data can be in many way between DB’s. Open two connections - one to Access & one to new DB then just copy & paste tables. CSV files or even spreadsheets can be used. Many posts on this forum on how.

If this answers your question please click on the :heavy_check_mark: (upper left area of answer). This also applies to your first post. Also you can upvote any answer you see fit by clicking the ^ in the same area. The upside down caret is a negative vote on the comment.

Thanks Ratslinger,

I’ve been feeling the connector was the method, but wasn’t sure it was the only method.

Can you comment on why my Libreoffice Base in Linux Mint 18.1 doesn’t have a connector (selection) for MS Access, but many other references and videos I’ve seen using Windows has MS Access listed as a DB type for which to connect?

Should my Libreoffice Base in Linux Mint 18.1have a connector for Access?

So if I succeed in connecting my Access mdb to Libreoffice Base, the new Libreoffice Base DB will consist of Two files, the mdb and the LOB file? Or Once its connected, the mdb data is a part LOB db file?

@Edward2 I will edit my answer with a reply because comments are not large enough.

btw, I don’t think he or she can vote here, until she or he has at least 5 points. So I gave him 10 so he could (i.e. up voted his question, as I do with all reasonable questions coming from someone w/ only 1 point). But I think the questioner can still click the :heavy_check_mark: to select the answer.

“If I Import an MS Access 2007 DB into LibreOffice Base in Windows, can the new db be used in a Linux installation of LibreOffice Base then?”

Once you get the data into a compatible database you will be able use it on Linux as well.

You should also manage your expectations. Access is more capable than LO. Moving tables to LO is like trying to get into a tight shoe with a big foot. It can be done if you skinny up the foot first. Recreating queries, forms and reports is a challenge, and this depends to a measure on how complex your stuff is.

There are several ways to migrate tables but I’ve not yet found one that works perfectly in all cases without some fixup once the migration is done. I think the last time I did it was w/ this tool that works pretty good, but is NOT perfect.

There are a number of gotchas in addition to the ones R mentioned. Watch out for field conversions, like for yes/no fields or currency. Also first clean your table names of special characters like colons. And you will have issues with case if you are not careful. Suggest dumbing down your Access first, then trying to migrate. Also LO can’t use =date() or =time() as a timestamp default. This can be done in MariaDb/MySQL but it isn’t as clean as in Access, i.e. only once the record is saved and refreshed will you see the default date/time inserted. Also watch out as you might loose your field comments and have to manually copy them over.

Forms have most all functionality as Access, but Reports seem to lack ability to grow and shrink with data, e.g. note fields are always of fixed height.

Also, so far, I haven’t figured out how to build macros that can be used inside query fields like was so handy in Access. For example I had a set of macros called things like: LastFirstName(First, Middle, Nick, Last) which would properly format a name from components into something like: Smith, Ron “Ronny” A. Now this has to be done in SQL which is less capable.

Tools like HeidiSQL work quite nice to help you along and runs in both Windows and Linux, but watchout for database names with spaces in them, and other special characters in table names which can cause issues.

Also you will loose lookups and other properties. LO does not have the ability to add a field lookup (pull down) in tables like in Access (to help you insert ID from human readable text, like for example when you are converting State codes like CA to 42). You will have to build a form to replace this functionality which is 20 times harder than doing this in Access.

I could go on, but I’ll stop there.

But all that being said, I have converted some of my Access to LO and I’m working on the rest.

It has been a painful process, but things are gradually looking up.

I am committed to moving away from Windows ever since W10 was shoved down our throats, with it’s “run my CPU like crazy whenever it wants” policy, coupled w/ forced updates. It just began to feel like my machine was now owned by MS, even though I paid for it. …end of rant.