Previous: CSV Files, Up: Interoperability


15.2 Importing MDB Files

Access files (mdb files) are collections of several relations, also known as tables. Tables can be either user tables storing user data, or system tables storing information such as forms, queries or the relationships between the tables.

It is possible to get a listing with the names of all tables stored in a mdb file by calling mdb2rec in the following way:

     $ mdb2rec -l sales.mdb
     Customers
     Products
     Orders

So sales.mdb stores user information in the tables Customers, Products and Orders. If we want to include system tables in the listing we can use the ‘-s’ command line option:

     $ mdb2rec -s -l sales.mdb
     MSysObjects
     MSysACEs
     MSysQueries
     MSysRelationships
     Customers
     Products
     Orders

The tables with names starting with MSys are system tables. The data stored in those tables is either not relevant to the recutils user (used by the Access program to create forms and the like) or is used in an indirect way by mdb2rec (such as the information from MSysRelationships).

Let's read some data from the mdb file. We can get the relation of Products in rec format:

     $ mdb2rec sales.mdb Products
     %rec: Products
     %type: ProductID int
     %type: ProductName size 80
     %type: Discontinued bool
     
     ProductID: 1
     ProductName: GNU generation T-shirt
     Discontinued: 0
     
     ...

A record descriptor is created for the record set containing the generated records, called Products. As seen in the example, mdb2rec is able to generate type information for the fields. The list of customers is similar:

     $ mdb2rec sales.mdb Customers
     %rec: Customers
     %type: CustomerID size 4
     %type: CompanyName size 80
     %type: ContactName size 60
     
     CustomerID: GSOFT
     CompanyName: GNU Soft
     ContactName: Jose E. Marchesi
     
     ...

If no table is specified in the invocation to mdb2rec all the tables in the file are processed, with the exception of the system tables, which requires ‘-s’ to be used:

     $ mdb2rec sales.mdb
     %rec: Products
     ...
     
     %rec: Customers
     ...
     
     %rec: Orders
     ...