Working with Microsoft Access databases

From NeoWiki

Microsoft Access is a widely-used database on Windows. Like Base, Access is capable of serving as a front-end to various databases and as a stand-alone database itself. As a result, Access files (.mdb) may be just a collection of forms, queries, and settings for connecting to an external database, or they may be forms, queries, and the actual data.

Unfortunately, Access is one of the least interoperable of Microsoft's many applications. This means that there are a very limited number of options for reading Access databases on anything other than Microsoft Windows. Even solutions that are available are typically limited; for instance, many offer read-only connections to an Access database, and any forms and saved queries developed in Access usually cannot be read or imported.

Contents 1 Potential ways of extracting data from an Access .mdb file 1.1 Connecting to Microsoft Access databases from NeoOffice 1.2 Converting a Microsoft Access database to a format useable in NeoOffice 2 Related External Links

Potential ways of extracting data from an Access .mdb file

Connecting to Microsoft Access databases from NeoOffice

N.B. To our knowledge, no one has tried any of the potential solutions below to verify that they work with NeoOffice.

• A Mac ODBC driver that works with MS Access files is the Actual ODBC driver. It is a commercial one ($29), but it works with Access. (There was a report that NeoOffice 2.0 Alpha worked with Actual's MySQL ODBC driver, but no one has tried the Access ODBC driver.)

• StelsMDB is a commercial ($110; 30-day free trial) MDB JDBC type 4 driver that allows users to perform SQL queries and other JDBC operations on Microsoft Access database files (MS Access 2000, 2002, 2003, XP versions). Presumably NeoOffice could use the JDBC driver to connect to the Access database.

• HXTT Access Pure Java JDBC (1.2, 2.0, 3.0, 4.0) Drivers for MS Access is a commercial ($300+; trial available) Type 4 JDBC driver that supports Access versions from 95, 97, 2000, XP, 2002, to 2003. Presumably NeoOffice could use the JDBC driver to connect to the Access database.

Converting a Microsoft Access database to a format useable in NeoOffice

N.B. To our knowledge, no one has tried any of the potential solutions below to verify that they work and produce output that can be used in NeoOffice.

• mdb2odb is/was a Spanish project to develop an Access-to-OpenDocument Database converter. The project's binaries were available for Debian/Ubuntu, so it is unknown if the code compiles on Mac OS X (or if the project still exists, as its website in non-functional as of this writing).

• If you have access to a Windows PC, there are additional options:
  • The MySQL Migration Toolkit for the Mac can be found in the MySQL GUI Tools Bundle and should be able to be used to move data from an MS Access database to a MySQL one (which NeoOffice will happily deal with).
    The Migration Toolkit is known to work with Access files on Windows; however, the migration process appears to require preliminary preparations to be done in Access before the migration can begin.
  • Export the data from Access into an Excel file, which NeoOffice can open in Calc or use as a data source for Base.
  • It may also be possible to install OpenOffice.org and use tools in Base and Windows to load the data and transfer it to another format.

Related External Links

• OOo wiki article on the features available in OOoBase when connected to an Access database (currently requires Windows)
• OOo wiki article on configuring OOoBase on Windows to connect to an Access database

• Other options and further discussion can be found on the Trinity forum: [1], [2], [3].