how to use an MS-Access file from Linux?

Question

I'm studying an introductory course in databases and one of the exercises is to work with MS-Access. However I'm using Linux at home and although I can use the computer classes at the university it is far from convenient (limited open time - my studying time is mostly nights).

So how can can use an Access file (*.mdb) in Linux, by use I mean changing tables, writing queries...

Are there tools to convert it to another database format (mysql, postgresql or even gadfly)?

Also what problems I may encounter?

Answer 1

From the documentation: Connecting To Microsoft Access. However, this seems to indicate that you need access running in a windows host and connect via ODBC... See also Known Problems.

Answer 2

Although a bit dated, I've had good success with mdbtools which is a set of command line tools for accessing and converting Access databases to other formats. I've used it for importing databases into PostgreSQL.

If you're running an Ubuntu variant you can install it with:

sudo apt-get install mdbtools

or you can download it from here.

Answer 3

You can work with Access through a connection (ODBC or OLEDB), as long as you only need to manage the "database" dimension of the file (tables and views, which are called "queries" in Access).

Once the connection is open (see here for connection strings), you can send SQL commands to your mdb database, such as (where cn is here a connection object):

cn.execute "CREATE TABLE myTableName (myTable_id autoNumber, myTable_code Text, ...)"

Please note that MsAccess uses a specific DDL that looks like the standard T-SQL but is not really it. Check the syntax in MsAccess help.

Depending on your database (and its constraints, default values, primary keys used, relations, data validation rules, aso), transfering Access can be easy and straight or might not even be possible. You will encounter a problem each time your database implement an access-specific/non-standard SQL rule.

If you really need to convert your access data to something else, I'd adise you to (1) export it under MS-SQL (the free version will be ok, an upsizing wizard is available in Access or on this site), (2)use an additional tool like this one to generate a "CREATE DATABASE" SQL Script, including or not data inserts, (3) use this script to try to create the database and its data on another database server.

Answer 4

I am currently trying Access with Wine on Ubuntu and I seem to be getting there. I have found that I need to copy various dlls manually, but that could easily be lack of reading up on the subject.

Answer 5

If you've got an assignment to work with Access, then frigging find a Windows computer and do your exercise on the native platform for Access. It's completely senseless to do anything else, as you won't be learning anything useful about Access.

If the assignment is to use a Jet data store, then that's something of a different story. And if it is, then you should have worded your question differently. I wouldn't recommend using Jet on anything but a native Windows file system. Certainly if the project is to actually read/write data to a Jet data file then you're not really fulfilling the assignment if you're not using Windows at least as the ODBC host.

--
David W. Fenton
David Fenton Associates

Answer 6

You're out of luck. Access has no real equivalent on Linux and while Kexi is an interesting alternative that can import Access files and aims to provide similar functionality, it doesn't actually uses Access files once the data is imported.

If your assignment is to develop an Access application with forms etc as opposed to just using and mdb database as a store, then you can try a recent release of Wine with a compatible Access version (see compatibility list) or, even better, find a Windows machine where you're sure it's going to work.

Not to be forgotten, the use of a Virtual Machine loaded with Windows would help you achieve the same thing on your Linux box.

Answer 7

Se my previous answer in a similar question. It's a litle script to do just that.

http://stackoverflow.com/questions/242975/access-db5-to-mysql-automatically#243069