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
…