Converting MS Access DB to OOo Base in Ubuntu

Discuss the database features
Post Reply
User avatar
BidAl
Posts: 20
Joined: Mon Jan 28, 2008 7:03 pm
Location: South west Wales

Converting MS Access DB to OOo Base in Ubuntu

Post by BidAl »

Hi all

Just coming over from the Microsoft world to Ubuntu and need to open/convert my business database from Access to Base. I am no techy guy though and do not know how to go about it. The "point and click" method didn't work for me. I thought that I could convert the Access database in the ODBC format but received an error message telling me that "OpenOffice.org 2.3 could not load the program library libodbc.so.1, therefore the ODBC data source selection is not available". I ok'd the message and Write opened with well over 5,000 pages of symbols. Not what I wanted at all.

Should I install OpenOffice on the Windows XP (SP2) machine, convert the database there then export it to my Linux box? or can I do it directly from one machine to the other over my LAN
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by Hagar Delest »

I move the thread to the Base forum.

I don't trust the Ubuntu version, you should install the official version of OOo : [Ubuntu] Installing OOo on Debian and Co.

Thanks to add '[Solved]' in your first post title (edit button) if your issue has been fixed.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
BidAl
Posts: 20
Joined: Mon Jan 28, 2008 7:03 pm
Location: South west Wales

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by BidAl »

Thank you for your prompt reply.

I have followed your link and printed out the information so that I can carefully follow the instructions. I'll let you know how I get on.
We all should try, some just try harder than others. There again, some don't bother.

OOo 3.1.X on Ms Windows XP
Linux - Centos on my server and second desktop
Linux - Mint on my testbed machine
User avatar
BidAl
Posts: 20
Joined: Mon Jan 28, 2008 7:03 pm
Location: South west Wales

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by BidAl »

Hi again Hager de l'Est
I have followed through the instructions and all went well up to the final part
cd desktop-integration
sudo dpkg -i openoffice.org-debian-menus_2.#.#-#_all.deb
I had removed all evidence of OpenOffice using Synaptic then downloaded the file to my desktop. Instruction 3 says Extract the tarball in a directory and following this instruction resulted in another file on the desktop called openoffice.org-debian-menus_2.3.1.9_all.deb.

CD'd into desktop-integration and typed (very carefully)

sudo dpkg -i openoffice.org-debian-menus_2.3.1.9_all.deb

The result was a two line error message:
1) dpkg: error processing openoffice.org-debian-menus_2.3.1.9_all.deb (--install):
cannot access archive: No such file or directory
3) Errors were encountered while processing.

As I am not at all familiar with Ubuntu or for that matter the command line in any program I thought I have better stop there and seek further guidance.

By the way, reading back through my first post I realised that it is not very clear that Ubuntu and Windows are on the same machine, booting through grub.
We all should try, some just try harder than others. There again, some don't bother.

OOo 3.1.X on Ms Windows XP
Linux - Centos on my server and second desktop
Linux - Mint on my testbed machine
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by Hagar Delest »

Run 'ls' in the desktop-integration directory to get the exact name of the file. Just type the beginning of it and hit the TAB key, the filename will be autocompleted.

It doesn't matter that both OS are on the same machine.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by DrewJensen »

OpenOffice.org under Linux does not work with MS Access files - it's pretty much that simple.

If you need to connect directly with an mdb or accdb file you need to be working under MS WIndows.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
BidAl
Posts: 20
Joined: Mon Jan 28, 2008 7:03 pm
Location: South west Wales

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by BidAl »

I found desktop-integration in the DEBS directory

Running "ls" resulted in a lot of files. Which one should I use please?
desktop-integration
openoffice.org-base_2.3.1-9_i386.deb
openoffice.org-calc_2.3.1-9_i386.deb
openoffice.org-core01_2.3.1-9_i386.deb
openoffice.org-core02_2.3.1-9_i386.deb
openoffice.org-core03_2.3.1-9_i386.deb
openoffice.org-core03u_2.3.1-9_i386.deb
openoffice.org-core04_2.3.1-9_i386.deb
openoffice.org-core04u_2.3.1-9_i386.deb
openoffice.org-core05_2.3.1-9_i386.deb
openoffice.org-core05u_2.3.1-9_i386.deb
openoffice.org-core06_2.3.1-9_i386.deb
openoffice.org-core07_2.3.1-9_i386.deb
openoffice.org-core08_2.3.1-9_i386.deb
openoffice.org-core09_2.3.1-9_i386.deb
openoffice.org-core10_2.3.1-9_i386.deb
openoffice.org-draw_2.3.1-9_i386.deb
openoffice.org-emailmerge_2.3.1-9_i386.deb
openoffice.org-gnome-integration_2.3.1-9_i386.deb
openoffice.org-graphicfilter_2.3.1-9_i386.deb
openoffice.org-headless_2.3.1-9_i386.deb
openoffice.org-impress_2.3.1-9_i386.deb
openoffice.org-javafilter_2.3.1-9_i386.deb
openoffice.org-kde-integration_2.3.1-9_i386.deb
openoffice.org-math_2.3.1-9_i386.deb
openoffice.org-onlineupdate_2.3.1-9_i386.deb
openoffice.org-pyuno_2.3.1-9_i386.deb
openoffice.org-testtool_2.3.1-9_i386.deb
openoffice.org-writer_2.3.1-9_i386.deb
openoffice.org-xsltfilter_2.3.1-9_i386.deb
We all should try, some just try harder than others. There again, some don't bother.

OOo 3.1.X on Ms Windows XP
Linux - Centos on my server and second desktop
Linux - Mint on my testbed machine
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by r4zoli »

First in your list "desktop-integratin" this is not file it's a folder which contains missing file.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by Hagar Delest »

Have a look again at the tutorial, I've detailed the process.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by Villeroy »

Talking about conversion from mdb to native Base with embedded hsql, you can go the route via csv export. Older versions of Ubuntu could import mdb files as read-only tables. This has been dropped for some reason (data corruption?).
MS Access can export csv and all meta-information (tables, columns, queries, relations). Without MS Access at hand you can install mdbtools from the Ubuntu repositories. They do not include the odbc drivers which would allow read-only import in Base. But mdbtools include command line tools such as mdb-export and mdb-schema.
The built-in hsql engine can import csv as Sliderule describes here: http://www.oooforum.org/forum/viewtopic.phtml?t=66702
Take care of the date fields. They should be exported as ISO-strings (1999-12-31).
While importing, take care of all column types and relations. It may be difficult to re-create all the foreign keys.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
BidAl
Posts: 20
Joined: Mon Jan 28, 2008 7:03 pm
Location: South west Wales

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by BidAl »

Thanks to everyone who tried to help me back there.

This Linux thing was just too much for me. Eventually gone back to MS. OOo works fine there. I needed to spend a lot more time on the learning curve and capitulated as personal time is too much of a luxury at present.

Learning the command line (and Vi) is a 'for the future' project.

BidAl
We all should try, some just try harder than others. There again, some don't bother.

OOo 3.1.X on Ms Windows XP
Linux - Centos on my server and second desktop
Linux - Mint on my testbed machine
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by Villeroy »

Everybody expects the Linux-side to deal with everything proprietary you throw at it.
Nobody uses any tools in the Windows world although there are plenty of them (and sooo easy to use). For instance, the excellent export and reporting tools in MS Access.
You can assume that there are perfectly working Access-drivers for other operating systems. For a Microsoft developer it should be very easy to compile those drivers. Microsoft will never publish anything like that.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
biobot
Posts: 1
Joined: Thu Oct 14, 2010 6:43 am

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by biobot »

I found this thread while searching for the same answer: http://www.mail-archive.com/ubuntu-bugs ... 68095.html

Apparently, this is a known bug in OOo 3.2 and the author recommends trying this:

sudo ln -s /usr/lib/libodbc.so.1 /usr/lib/libodbc.so

Which I have done. The command worked without error, except that I did it two times, once with a .1 at the end.

I still get the same error. The JDBC option may be the best way, but I don't know enough to create the parameters.
OpenOffice 3.2 Xubuntu
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by RPG »

Hello

See in the tutorial sectie of this forum

http://user.services.openoffice.org/en/ ... 83&t=25300

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by Villeroy »

biobot wrote:I found this thread while searching for the same answer: http://www.mail-archive.com/ubuntu-bugs ... 68095.html
That bug has nothing to do with the inability to import MS Access into any database frontend on a non-Windows system. As a matter of fact, MS will never publish any non-WIndows drivers for their databases.

All ODBC relies on drivers. These drivers are provided by the original vendor of the database management software. It is far from trivial to reverse engineer the JET engine used by MS Access. One project had to give up as soon as they started writing into JET databases without destroying the whole thing.

[*.mdb] <--> [MS Driver(win only)] <--> [ODBC, ADO] <--> [any tool Base, Access, whatever]

The bug you linked affects ODBC under Linux.

Again: Put your *.mdb files on a WIndows machine and let Access export all the data as dBase tables (or csv). Run all the self-documenting tools in Access so you have all the meta-information about that database in some readable file format.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
josefg
Posts: 1
Joined: Thu Feb 10, 2011 12:08 pm

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by josefg »

Would it be possible to install the JET database engine under Wine, and then access it via unixODBC in some way?
Ubuntu 10.04, OOo 3.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting MS Access DB to OOo Base in Ubuntu

Post by Villeroy »

josefg wrote:Would it be possible to install the JET database engine under Wine, and then access it via unixODBC in some way?
You can try?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply