How do I import data from MS Access and create Metadata?

Edit Subject

Hi Steve,

MS Access uses a proprietary binary format to store the data, so the text metadata editor can't be used. However, it is possible to read the file using ODBC. It might be necessary to have MS Access installed, but the drivers are often pre-installed even if Access is not present.

There are two ways to set up a connection to MS Access using ODBC. The first option is to set up a named Data Source (DSN), the latter is to use a specific JDBC URL.

In order to define a DSN (Data Source name), open the "Data Sources (ODBC)" control panel in "Administrative Tools" and add a new "User DSN" for the selected file. Once a DSN is defined, go to DQ Analyzer, right-click Databases and open the Create New Database Connection dialog. Choose ODBC as the driver type and type the DSN as the connection name.

In the second approach, we omit the DSN definition part and go to the Create New Database Connection dialog directly. Again, choose ODBC driver, but this time, check the "By URL" radio button and type the following string to the Connection string field:
jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\data\test.mdb
where DBQ is the name of the MS Access file (C:\data\test.mdb). Be sure to use "Test Connection" to see if everything worked fine.

Also note that the ODBC driver exclusively locks the file, so it can't be open by any other application (say, MS Access).

Let us know if this worked for you.

The second approach worked quite well. I needed to save the database as Access 2003 format (.mdb). Thank you!

Great. Access 2007 required a slightly different driver name, the following worked for me (MS Access 2007 needs to be probably installed on the machine):

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\data\test2.accdb

This driver seems to support both 2003 and 2007 formats.