Page 1 of 1
Converting MS Access DB to OOo Base in Ubuntu
Posted: Mon Jan 28, 2008 7:35 pm
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
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Mon Jan 28, 2008 8:13 pm
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.
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Mon Jan 28, 2008 9:22 pm
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.
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Fri Feb 01, 2008 9:06 pm
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.
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Fri Feb 01, 2008 9:17 pm
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.
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Fri Feb 01, 2008 9:17 pm
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.
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Sat Feb 02, 2008 1:57 pm
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
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Sat Feb 02, 2008 2:37 pm
by r4zoli
First in your list "desktop-integratin" this is not file it's a folder which contains missing file.
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Sat Feb 02, 2008 3:17 pm
by Hagar Delest
Have a look again at the tutorial, I've detailed the process.
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Sat Feb 02, 2008 8:04 pm
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.
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Thu Jun 25, 2009 2:52 pm
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
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Thu Jun 25, 2009 4:46 pm
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.
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Thu Oct 14, 2010 7:18 am
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.
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Thu Oct 14, 2010 11:12 am
by RPG
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Fri Oct 15, 2010 11:51 pm
by Villeroy
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.
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Thu Feb 10, 2011 12:59 pm
by josefg
Would it be possible to install the JET database engine under Wine, and then access it via unixODBC in some way?
Re: Converting MS Access DB to OOo Base in Ubuntu
Posted: Thu Feb 10, 2011 2:22 pm
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?