Working with Microsoft Access databases

From NeoWiki

(Redirected from Miscellaneous Database Tips)
Jump to: navigation, search

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 it is unlikely that any solution will support reading or importing any forms or saved queries developed in Access.

Contents

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.

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.

  • MDB Viewer for Mac is a $5.99 application available in the App Store that supports viewing Access '97, 2002 and 2003 databases (tables only; no forms, queries or reports). It can also export tables to CVS files or to MySQL or PostgreSQL SQL dumps.
  • 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 is 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

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


This article in other languages: Français
Retrieved from "https://neowiki.neooffice.org/index.php/Working_with_Microsoft_Access_databases"
Personal tools