How to open an MS Access database with OO Base in Linux

capot · General User Joined: 26 Dec 2006 Posts: 11 Location: Texas

I've got OOo V2.1 installed on my Mandrake 10.1 Linux box. I'm having a real problem getting an Access database to open. I've got unixODBC installed which I read somewhere was a requirement, though I don't think its configured correctly. When I try to 'connect' to the database, OO Base crashes although I don't get any type of output from the crash. I was hoping it would just be a matter of clicking on the Access DB and it would open, however that has turned out to not be the case. Any and all help would be much appreciated.

Thanks
Chris

Ewald · OOo Advocate Joined: 16 Dec 2005 Posts: 392

there is no way to my knowledge that you open a MSAccess Database with OOoBase in a Linux environment just like that. Moreover, you won't be able to use MSAccess Forms at all. It is relatively easy to make the data stored in your MSAccess tables available. To do that with OOo Calc you have to create a OOoBase Database in the Windows environment with the Acess Data imported. That then can be used in the Linux environment.
here some links for further details http://www.oooforum.org/forum/viewtopic.phtml?t=39816&highlight=
http://www.oooforum.org/forum/viewtopic.phtml?t=40625&highlight=

I personally am still looking to replace MSAccess by a suitable Linux compatible application and am doing so for the past two years. The best bet at the moment I think is KNODA (www.knoda.org). With the latest version 0.8.3. you can open MSAccess Databases under Linux just like that. But you'll also have the problem that the Forms won't work.

elven · General User Joined: 18 Aug 2005 Posts: 10

As I am currently facing a similar problem (without the crashes) as the original poster (capot), I might be able to add my two cents here... and a new question:

I work on Ubuntu Edgy and until a few days ago I had the OO-Version 2.0.4 from the Ubuntu repository installed. I remember that in the dropdown box "connect to an existing database" there was an entry called "Microsoft Access", enabling me to open an existing MS Access Database.

Unfortunately, due to a bug in the Ubuntu-version of OO, I recently switched to version 2.1 from the OpenOffice-website (installing from the deb-package). That Ubuntu-bug is gone now, but like the original poster in this thread (http://www.oooforum.org/forum/viewtopic.phtml?t=51009) I am now missing the "Microsoft Access" entry in the dropdown box.

So, there clearly must be a way to open an MS Access database on Linux systems! Could it be that the Ubuntu version contains some additional drivers (or whatever) that the original version is lacking? And does anybody know how I can re-install them?

TIA,
marcus

Ewald · OOo Advocate Joined: 16 Dec 2005 Posts: 392

According to my knowledge the MSAccess driver is part of the Windows OOoBase but not the Linux version. But as I mentioned above, if you only have the Acces database and no access to a Windows machine, then you can use KNODA to open an MSAccess Database in Linux, save it in MySQL Format and then open it with OOoBase. If you do have acces to a windows machine, open the MSAccess database with OOoBase and copy the tables to a new OOoBase database which then can be opened on the Linux machine.

Always keep in mind though, you only gain access to the tables. Queries, Forms and Reports won't work and you'll run into various problems when your original database contains fields with the datatypes Binary and Date/Time.

elven · General User Joined: 18 Aug 2005 Posts: 10

Thank you for your prompt reply.

I do realize that in theory I would need Windows to access an Access-file... however, I clearly remember that I found the "Microsoft Access" entry in the dropdown box under Ubuntu Edgy.
Before switching to 2.1 I was able to open two MS Access databases in OpenOffice under Linux.

What am I missing here?

elven · General User Joined: 18 Aug 2005 Posts: 10

<bump>

Unfortunately, I still haven't solved this issue.
Is anybody able to help me?
Please?

MarcioAB · Posted: Thu Jan 11, 2007 5:10 pm Post subject:

Hello Elven,

In order to connect with MS database engine (that I think is called MS Jet 4) you must have acquired these drivers somewhere.

I have a rig with OpenSuse 10.1 and OO.o 2.0 (build 2.0.2.15) and there is no MS Access in the dropdown list (just double-checked now before post here).
So, there is no driver on 2.0.2.15 and OpenSuse 10.1.
Probably there will be no driver on 2.0.4 unless something odd have leaked but any way that was "fixed" and there is no driver in 2.1.

I do not know Ubuntu, but lets assume they don't want to fight with MS and deliver MS database engine drives with their builds.

So, the only thing I can imagine is that somehow, you must have downloaded these drivers and/or something you installed should have included them AND the OO.base for Linux recognized them.

Anyway, it all seems that is against the flow and you should not expect a natural, supported connection of MS Access from OO.o in Linux environment.

My advise is: Migrate the database from JET to HSQL

1. Find a Windows XP machine with OO.o
2. Copy the xxx.mdb files into that machine.
3. Create a new OO database from a connection to the xxx.mdb (MS Access)
4. Create a new OO database type HSQL (the standard OO.base)
5. Copy/Paste the connection into the HSQL (you need to know all the columns information - unfortunately)
6. Save the HSQL database in OpenDocument format
7. Goto your Ubuntu machine, forget MS Access and good luck !!