Ask Your Question
1

migrating from MS-Access

asked 2018-04-22 08:42:35 +0200

YossiN gravatar image

yes, this question was asked many times with the answer "no" but I'm hopeless optimist :-)

I want to migrate my app from ms-access to LO (yes, I know it's re-developing it) the issue is with the data

Viewing the net I saw many times the "no trivial solutions" but since there is a lot of data. maybe there is a paid way of doing it?

other ideas?

thanks

edit retag flag offensive close merge delete

Comments

I migrated all my data and most of my apps to Base. It hasn't been easy. Keep in mind that it's like trying to get a larger foot into a small shoe. There will be many things you will have to simplify and rethink. There are still many things missing from Base that frustrate me, e.g. simple cutting and pasting cells, rows or columns. But MS wasn't fixing the old bugs in newer versions of Access. Rather they were making it worse. At least LO Base has ongoing bug fixes and is improving.

EasyTrieve gravatar imageEasyTrieve ( 2018-05-12 21:38:56 +0200 )edit

Another thing is start with the simple stuff. If you start w/ the hard stuff you'll probably just get frustrated and quit. Figure out how to just move one table. Issues that you will need to work on are many: field types, e.g. check boxes (I use TINYINT (1)), timestamps (I use DATETIME defauting to CURRENT_TIMESTAMP), and Memo to TEXT. Again use HeidiSQL to help with this. Also I think I used http://www.bullzip.com/products/a2m/i... and then had to hand fix some of the fields.

EasyTrieve gravatar imageEasyTrieve ( 2018-05-12 22:01:39 +0200 )edit

4 Answers

Sort by » oldest newest most voted
1

answered 2018-04-22 17:26:21 +0200

Steve R. gravatar image

updated 2018-04-23 02:00:06 +0200

There are many approaches to migrating from MS Access to a Linux based system. The approach I utilized was to export the MS Access data to MySQL (you can also export to MariaDB) as a back-end. You can then use Base or an HTML/PHP approach as the front-end to access your data.

If you have not done so already, visit the OpenOffice forum as there is a wealth of information concerning Base. I favor the HTML/PHP approach.

edit flag offensive delete link more

Comments

+1 I also used the Access --> MariaDB approach. It's not straightforward, but it will get you there (in terms of moving the data). BTW, I also back-ported MariaDB to MS Access so I had an operational system as I continued development in Linux. Also I've found that HeidiSQL is a great tool to help out on Linux under running inside of Wine. In fact I now use HeidiSQL exclusively as my go to when designing and restructuring my data. Then use LO as a front end for Forms.

EasyTrieve gravatar imageEasyTrieve ( 2018-05-12 21:24:35 +0200 )edit
1

answered 2018-04-23 02:36:08 +0200

Ratslinger gravatar image

Hello,

About the only thing you are going to be able to move is the data. For forms & reports nothing known here. If you just want a connection to the the Access tables (and possibly queries) from Base you can do this through the built-in connector or through a JDBC connector (depends upon OS & LO bitwise). See this post -> mdb files not loading to base. Your question lacks information so a note here. From the connector website:

Supported Access formats: 2000, 2003, 2007, 2010.

Now if you are looking to transfer data from the Access files to another DB that question is not relative to LibreOffice.

However, here is one way. You can use SQL Workbench/J, a cross-platform SQL query tool. You can use the UCanAccess JDBC connector referred to in the first link and whatever JDBC connector is needed to to connect to the database you have chosen to use. Once you have the connectors installed & working, there is a tool built in to SQL Workbenck/J called 'Data Pumper...". This will allow you to move the data between various databases you have connections to.

Just tested moving tables from .mdb & .accdb files to a MySQL database. Have used this for other data transfers in past also as well as numerous other SQL activities. SQL Workbench/J & UCanAccess connector are cross platform.

If this answers your question please tick the ✔ (upper left area of answer). It helps others to know there was an accepted answer.

edit flag offensive delete link more

Comments

With the huge help of your earlier post, I succeeded in bringing a reasonably (2003?) old but complex Access db into Base. I got tables and relationships, but no queries or forms (of course). Still, it was a great start! (Good think my main machine is Ubuntu -- I cannot convince my MacBook's Base to find the JRE/JDK installed on it -- despite all your posts about it here!)

David gravatar imageDavid ( 2018-04-23 09:20:14 +0200 )edit

download the X86 version of Java and Libre....the 64bit version seems to be a headache....I got it to work eventually

robaed gravatar imagerobaed ( 2018-10-09 16:33:07 +0200 )edit

@robaed Which Java to install depends entirely upon the bitness of the LO version installed & the OS being used. The X86 version will NOT work on Mac as it is 64-bit.

Ratslinger gravatar imageRatslinger ( 2018-10-09 16:49:26 +0200 )edit
0

answered 2018-04-22 14:00:09 +0200

David gravatar image

This is a bit of a "non-answer", and hopefully others with more experience will have something of value. This is just a pointer to a couple links which might help with the "learning curve".

(1) I found it helpful reading through a bug report discussion(!): "UI - Reducing user confusion: Queries, Views and Tables". I was reasonably adept at using MS Access years ago, and have been procrastinating learning Base (although I think my time has come). This thread clarified some concepts which I would be struggling with otherwise.

(2) You will see I've been procrastinating since 2012 in this link: I was interested then in sample databases for Base, and that Q&A has gathered some useful resources. It has been updated from time-to-time, so it isn't hopelessly out of date.

I hope this helps why you wait for something more substantial.

edit flag offensive delete link more
0

answered 2018-10-09 16:21:50 +0200

robaed gravatar image

updated 2018-10-09 16:28:07 +0200

Definitive Answer....Export your MS ACCESS database to .Xls.....what's even "SWEETER" you don't have to even create a table ahead of time to accept the data...Simply Open the .xls in libre calc....copy the data like you see in the video ...open Librebase....then click PASTE....all easy from there

https://www.youtube.com/watch?v=D_W-F...

edit flag offensive delete link more

Comments

1

This process has been around for some time. You still may have data type problems doing this. Not creating a table ahead also requires you to have column headers in the spreadsheet for use as field names. You also do not get any queries you may have gotten if you use a connector instead to get to the Access DB. Nothing is absolutely clean when moving data from one DB to another.

Ratslinger gravatar imageRatslinger ( 2018-10-09 16:55:20 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2018-04-22 08:42:35 +0200

Seen: 1,731 times

Last updated: Oct 09 '18