Importing MS Access

[DWFII]

I bet this question has been beat to death but I am wanting to migrate from Microsoft Office (97) to Open Office. I do have some Access databases that I would hate to have to re-create. Will Open office import Access databases?

[r4zoli]

[Tutorial] MS Access and OOo Base

[DWFII]

[Tutorial] MS Access and OOo Base

Thank you for the response..

After posting my question I stumbled upon that link.

Basically, if I read it correctly, it says that converting MSAccess databases to OO data bases is difficult in the extreme if not impossible.

And I don't understand why. MSAccess is one of the most popular database programs in the world if only because it is a part of MS Office. It would seem a no-brainer to encourage people to use OO. One of the things that first drew me to OO was the ability to import Word docs and retain the formating. Similarly to import Excel documents.

[sigh]

[squenson]

And I don't understand why. MSAccess is one of the most popular database programs in the world if only because it is a part of MS Office. It would seem a no-brainer to encourage people to use OO. One of the things that first drew me to OO was the ability to import Word docs and retain the formating. Similarly to import Excel documents.

This is very, very complex and dangerous. You have to:
1) Understand the proprietary, never published, MS Access file format
2) Write conversion programs to adapt to the constraint of Base
3) Once 1) and 2) are completed, be ready to face the Microsoft lawyers, as they consider the file format as a trade secret and reverse-engineering such thing is forbidden.

Oh, of course, you have to do this for free!

[DWFII]

Isn't this the same problem as with importing Word or Excel formats?

For that matter, does Open Office import Dbase files? Paradox can. MSAccess can export to DBase. If OO could import DBase the problem is solved. Or is it?

The issue is getting the MSAccess information into Open Office in the correct format and then saving it in OO format.

[squenson]

If you just need the data, then this is easy: use an ODBC driver to connect to the MS-Access database, copy the tables and that's it. But MS-Access is much more than that: queries, forms, macros, reports.

[DWFII]

If you just need the data, then this is easy: use an ODBC driver to connect to the MS-Access database, copy the tables and that's it. But MS-Access is much more than that: queries, forms, macros, reports.

I am a long, long way from being a database wizard. I have used MSAccess for a while and done tables, simple queries, forms and reports. I think if I could just get the tables...with accurate data...into OO, I would be happy. But I have no idea what an ODBC driver is or how to use it.

[TheGurkha]

Base isn't a database engine. It is a bridge bwteeen database engines such as MySQL, Dbase, paradox, postgreSQL and so on and OOo documents. The database engine that is bundled with OOo is actually HSQLDB.

You might be better off using another database program altogether, one that Access can export to or one that can import from Access, and then using Base to act as a front-end and bridge between that database and your OOo documents.

[Villeroy]

It would seem a no-brainer to encourage people to use OO.

I think there is a radical misconception about the high level of complexity in software, particularly when the source code is not availlable.
OOo (Star Office) has been developed in competition to MS Office since 20 years and still the Word/Excel/Powerpoint filters are not 100% compatible.
There is a group of developers who spent months writing a Linux driver for MS Access files. The project has been canceled because writing to the tables could destroy the database for unclear reasons. Read-only access is safe and possible with that driver.
For Microsoft developers it would be a matter of hours to compile such a driver for any compter platform, since they have the source code.

Quite often this type of topics appear like: "Why can't OOo support any file format of computing history? (and still be freely installable on a home PC rather than super-computer)"

[newuser]

If you just need the data, then this is easy: use an ODBC driver to connect to the MS-Access database, copy the tables and that's it. But MS-Access is much more than that: queries, forms, macros, reports.

I am a long, long way from being a database wizard. I have used MSAccess for a while and done tables, simple queries, forms and reports. I think if I could just get the tables...with accurate data...into OO, I would be happy. But I have no idea what an ODBC driver is or how to use it.

To put it simple an ODBC data source is a database server which runs using the embedded database engine installed along with Windows XP. By default it can use dbase, access, paradox and foxpro tables, other drivers are available from other vendors.

You can create an odbc using start->all programs->administrative tools->odbc data source, choose your file and then you will be able to connect to it from Ooo Base. Remember that when you connect to your file you are actually using it, so use a copy or make a backup.

The ODBC engine is the fastest server available under Windows XP, on my 8 years old pc running Ooo it executes queries that involve more than 10,000 records in less than a second (options "run sql directly" must be selected)

[DWFII]

If you just need the data, then this is easy: use an ODBC driver to connect to the MS-Access database, copy the tables and that's it. But MS-Access is much more than that: queries, forms, macros, reports.

I am a long, long way from being a database wizard. I have used MSAccess for a while and done tables, simple queries, forms and reports. I think if I could just get the tables...with accurate data...into OO, I would be happy. But I have no idea what an ODBC driver is or how to use it.

To put it simple an ODBC data source is a database server which runs using the embedded database engine installed along with Windows XP. By default it can use dbase, access, paradox and foxpro tables, other drivers are available from other vendors.

You can create an odbc using start->all programs->administrative tools->odbc data source, choose your file and then you will be able to connect to it from Ooo Base. Remember that when you connect to your file you are actually using it, so use a copy or make a backup.

The ODBC engine is the fastest server available under Windows XP, on my 8 years old pc running Ooo it executes queries that involve more than 10,000 records in less than a second (options "run sql directly" must be selected)

Walk me through this, if you don't mind. I am totally clueless.

My concern is with Windows 7 x64. I can't run my old version of MSAccess on Windows 7 reliably. That's why I want to get this data into OO.

[newuser]

A step by step guide to create an ODBC data source:
http://www.truthsolutions.com/sql/odbc/ ... bc_dsn.htm

To connect to it open Base, select connect to an existing database, choose ODBC and select the data source you have just created.

I stress out the fact that if you create a data source on your access file you will be using it in Ooo and changes will be stored immediately into it. For example if you delete a table it will be erased, no need to issue a save command from the file menu.

[Villeroy]

Walk me through this, if you don't mind. I am totally clueless.

start->all programs->administrative tools->odbc data source, choose your file and then you will be able to connect to it from Ooo Base.

Got this? Fine.

In OOo File>New>Database...
[X]Connect to existing database
Type: ODBC
Enter the name of your set up ODBC datasource.
Register the database if you want to use it with Writer or Calc.
Store the "database document" which does not contain anything but this connection set up. Your data are still in the mdb file, but now you can use them in Base and Base can serve them to office documents.

[*.mdb file]<-->[Microsoft drivers]<-->[ODBC config]<-->[Base config]-->[Calc, Writer]
This can not work on non-Windows systems since Microsoft will never release any drivers.

[MyBookie]

I'm sorry to resurrect an old thread, but I thought that would be better than starting a new one which is so similar. I imported an old Access 2000 database into OpenOffice Base successfully, but cannot edit it, or add new records. I'm sure I am doing something dumb, but I'm kind of at a loss.

I did attempt to create an ODBC Data Source as recommended in the OpenOffice knowledge base http://www.openoffice.org/FAQs/ms-access/ms-access.html , but it said to Select the Microsoft Access Driver from the box, but the only option I see is SQL.

[Villeroy]

I'm sorry to resurrect an old thread, but I thought that would be better than starting a new one which is so similar. I imported an old Access 2000 database into OpenOffice Base successfully, but cannot edit it, or add new records. I'm sure I am doing something dumb, but I'm kind of at a loss.

I did attempt to create an ODBC Data Source as recommended in the OpenOffice knowledge base http://www.openoffice.org/FAQs/ms-access/ms-access.html , but it said to Select the Microsoft Access Driver from the box, but the only option I see is SQL.

1. You did not import anything at all. You connected a Base document to your old Access database in the same way as you can connect Access documents to certain 3rd-party databases. If you could edit the tables you would not edit the Base document. You would edit your .mdb file and when you reopen that file in MS Access you would see the modifications. This is very important to understand because this is how "normal" databases are supposed to work and it makes a huge difference. Just have a look at the status bar of your Base document to get info about the connected database. This connection utilizes a Microsoft database driver because only Microsoft knows how to read JET databases. JET is the database embedded in most .mdb files. This connection is not portable to the Mac nor Linux because MS does not publish the JET database driver for any platform other than Windows.
2. Base utilizes primary keys to fetch records for write access. A table with no primary key is not editable.

[MyBookie]

Thank you for your patience with me. I do not need this data to stay as an mdb file - I am fine with it permanently becoming a Base file. Can you help direct me to know how to convert it permanently to a Base file? I tried following the tutorial, but got stuck when I didn't see Access as a choice when attempting to create an ODBC Data Source. Thank you again.

[Villeroy]

I recommend strongly that you keep your database as is with a working copy of MS Access. Whenever you need those data in the context of OpenOffice, your read-only database will serve you those data for serial letters, spreadsheet calculations or whatever.

[MyBookie]

Thank you for the assistance Villeroy! You have solved my problem. The mdb file did not have a primary key. Now that I assigned one, all is well. Thank you very much.