IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
10 replies Latest Post - ‏2012-02-10T07:40:41Z by SystemAdmin
33 Posts

Pinned topic Database connection to MS ACCESS

‏2011-03-09T09:47:03Z |

I'm trying to set up a database connection for my ODM datasource using the Generic JDBC

I set up the connexion string (URL) as follows :

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:/mydb.mdb

1) Although my db has no user name neither password, it seems that these fields are mandatory. Could we leave them blank , or otherwise, what should we fill in ?

2) The jar list is empty which also triggers an error message. What jar should we set here ?


Updated on 2012-02-10T07:40:41Z at 2012-02-10T07:40:41Z by SystemAdmin
  • raghu1987
    5 Posts

    Re: Database connection to MS ACCESS

    ‏2011-05-28T10:53:34Z  in response to davidoff
    Davidoff, did you find the answer to question. If yes, can you please post it here, as I too am facing similar problem.
    • davidoff
      33 Posts

      Re: Database connection to MS ACCESS

      ‏2011-05-29T19:42:13Z  in response to raghu1987
      No. Sorry. Unfortunately, I did not receive an answer.
    • Michel Eisenmann
      Michel Eisenmann
      15 Posts

      Re: Database connection to MS ACCESS

      ‏2011-06-01T09:19:55Z  in response to raghu1987

      The following page explains how to achieve what you want.


      • Michel Eisenmann
        Michel Eisenmann
        15 Posts

        Re: Database connection to MS ACCESS

        ‏2011-06-03T10:53:19Z  in response to Michel Eisenmann
        I just got notified (thanks Alex) that you may not be able to access the page I pointed to in my answer. Here is a copy of the page advices...

        IBM ILOG ODME 3.4 was shipped with a great new feature, that allows to import an external database schema as the base for creating the Application Data Model your application is based on. The IBM ILOG ODME IDE 3.4 comes with a pre-installed set of drivers, all based on JDBC, that allow you to directly connect to DB2, Derby or Oracle databases.

        Unfortunately, because of a very limited support of the ODBC JDBC driver (you can read this post from the Eclipse DPT mailing list) that cannot introspect the database catalog, we simply could not provide this feature for the widely used Microsoft Access database type.

        This document describes how you can work this limitation out, using a freely available driver that does the right job. We sincerely hope this will help you simplify the integration of your data within your IBM ILOG ODME application.
        Getting and installing a proper MS Access database driver

        The first thing that must be done is find and install a JDBC driver that allows to connect to MS Access database files. Fortunately, there is one available.

        J-Stels Software is a software publisher that, among others, provides a pure-Java MS Access driver based on JDBC that does provide the feature we were missing.

        Go to the product download page and select StelsMDB JDBC Driver 1.3.
        Extract this file on your hard drive. We will presume that you installed this to C:\Java\stels_mdb.
        You should now have a directory in C:\Java\stels_mdb\mdbdriver with, at least, the following jar files:

        Adding this driver to the IBM ILOG ODME IDE

        The next step is to create a Driver Definition entry in Eclipse so that you can connect to your database, and query the schema. Note that you will do this once for all: next time you run the IBM ILOG ODME IDE, this new driver will be properly set up and available.

        Run the IBM ILOG ODME IDE
        Open the Preferences window (menu entry Window / Preferences) and select the Data Management / Connectivity / Driver Definition item in the tree.
        The window should look like: Preferences window
        Press the Add... button. The New Driver Definition dialog appears.
        In the Name/Type tab, select the Generic JDBC Driver item in the 'Available driver templates' list, and enter a name in the 'Driver name' text field. For homogeneity, we will use a name similar to the ones provided by default with ODME 3.4, ODM Enterprise MSAccess/JDBC Default.
        The dialog should look like: Editing the New Driver Definition - Page 1 of 3
        Switch to the Jar List tab, and press the Add JAR/Zip... button. Browse to the directory where you installed the J-Stels drivers (C:\Java\stels_mdb\mdbdriver, in our example), and add the four jar files.
        The dialog now looks like: Editing the New Driver Definition - Page 2 of 3
        Finally, in the to the Properties tab, setup the initial driver parameters:
        Connection URL: the JDBC connection string, that must start with jdbc:jstels:mdb:.
        Database Name: how the connection will appear in Eclipse.
        Driver Name: must be jstels.jdbc.mdb.MDBDriver.
        User ID: is not really relevant for MS Access.
        The dialog finally shows: Editing the New Driver Definition - Page 3 of 3
        Confirm all this by pressing the OK button
        You can now close the Preferences window by pressing the OK button.

        Your IDE is now able to connect to an MS Access database file, and perform all sorts of operations. The one we initially wanted to allow is schema import within an Application Data Model.
        Importing an MS Access schema within an IBM ILOG ODME Application Data Model

        The procedure to import (Reverse Engineer) a database schema into an ADM is described in the IBM ILOG ODME 3.4 documentation set. However, this is a short version of it, targeting our initial objective.
        For the sake of this example, we have use the famous 'Northwind database sample' that ships with Microsoft Access (that was converted to Access 2000 into a file located in C:\MyApplicationDir\Data\Nwind2002.mdb). Of course, your own *.mdb file will do just fine.

        Switch to the Data perspective (menu item Window / Open Perspective).
        If you don't have any Data Project yet, create a new one.
        In the Data Project Explorer, right-click the target data project, and select New / Physical Data Model. Give your model a name, make sure the Database type is set to 'Generic JDBC', and that you have checked the Create from reverse engineering radio button. This is how the wizard should look like at this point: image Press the Next button.
        In the Select Connection wizard page, press the New... button. This is where you indicate the parameters that specify what database must be accessed, and how:
        In the Connection Parameters page, select the ODM Enterprise MSAccess/JDBC Default entry in the JDBC driver drop-down list, then fill the connection parameters (essentially, the path to the MDB file you want to connect to).
        This is how the wizard page looks after we updated it: image
        Press the Finish button to connect to this MS Access database file.
        The new connection (MSAccessDB, in this example) appears in the Connections list. Select it and press the Next button.
        MS Access database files are read as having one single schema, called default. Check this item and press the Finish button.

        The MS Access schema is imported. In the Data Project Explorer view, you can now drill down your data model, and see the tables that were read from the MDB file.

        If you want to create a default diagram on this schema, simply right-click the default schema, and select the Add to Overview Diagram option. Then check the default schema item, and press the OK button. You will get exactly what you expected: Final diagram inferred from the MS Access database file
  • SystemAdmin
    151 Posts

    Re: Database connection to MS ACCESS

    ‏2012-02-10T07:40:41Z  in response to davidoff
    First, thanks for your answer, it was very usefull for me. Just one question, the driver and the procedure that you wrote is independent of the operative system?

    thanks in advance