Connecting Ignition to Microsoft Access

Ignition (formerly known as FactoryPMI) is an industrial software platform for developing Supervisory Control and Data Acquisition (SCADA), Human Machine (HMI) and Manufacturing Execution System (MES) applications.

SCADA software is used to monitor and control industrial processes that exist in the physical world.

An HMI is the input-output device through which an operator controls an industrial process. An HMI device presents data to the operator, for example it can present diagnostic data.

MES software is used to supervise and track work on a plant floor.

Ignition can connect to databases such as Microsoft SQL Sever and MySQL, enabling SCADA applications to expose external data, store historical information, and integrate with other systems. This article will concentrate on connecting Ignition to Microsoft Access, as that is the database our customers are using with this application.

Ignition is written in Java. To interact with a database, a Java application uses a JDBC driver. JDBC is a Java application programming interface (API) that connects Java to relational databases (and other tabular data, such as spreadsheets and flat files). A JDBC driver implements the JDBC API for a particular database.

Microsoft do not produce a JDBC driver for Jet (the underlying database engine for the Microsoft Office product suite) or Jet's successors the Office Access Connectivity Engine and the Access Database Engine. However, Microsoft do produce an ODBC driver for the Office suite. (ODBC is another data access technology, the Microsoft implementation of which is included with Windows.) To provide a JDBC interface to Office applications by using this native Microsoft interface, a JDBC driver must be able to convert JDBC calls to ODBC calls. As far as the Java application is concerned, it is using a normal JDBC driver. As far as the Office application is concerned, it is being accessed via the normal ODBC driver.

The Easysoft JDBC-ODBC Bridge is a client/server product that enables a Java application such as Ignition to access an ODBC database such as Microsoft Access. The client part of the Easysoft JDBC-ODBC Bridge is a JDBC driver. On Windows, the server part is a Windows service that acts as an ODBC application.

Installing the Easysoft JDBC-ODBC Bridge

If you have not already done so, please register with us to download a fully functional trial version of JDBC-ODBC Bridge.

Download, install, and license the JDBC-ODBC Bridge server for Windows. On the machine where the JDBC-ODBC Bridge server is running, configure a 32-bit ODBC data source for Microsoft Access in ODBC Data Source Administrator. To do this on 64-bit Windows, you need to use the 32-bit version of ODBC Data Source Administrator, which you run by entering the following command in the Windows Run dialog box:

%windir%\syswow64\odbcad32.exe

Use one of the test applets included in the JDBC-ODBC Bridge distribution to check that you can access the ODBC data source through the JDBC-ODBC Bridge. You may need to alter your Java security settings to allow the applets to run.

For information about installing, licensing, and testing JDBC-ODBC Bridge, refer to the Installation and Configuration chapters of the JDBC-ODBC Bridge User Guide.

Connecting Ignition to Microsoft Access

  1. Log in to Ignition Gateway (http://localhost:8088).
  2. Choose Configure on the main menu, then in the sidebar, choose Databases > Drivers.
  3. In the Database Drivers & Settings page, choose Create New JDBC Driver...
  4. In the New JDBC Driver page, in the JAR Files(s) section, browse for EJOB.JAR.

    On 64-bit Windows, EJOB.JAR, the JDBC driver part of the Easysoft JDBC-ODBC Bridge, is located in:

    drive:Program Files (x86)\Easysoft Limited\Easysoft JDBC-ODBC Bridge\Jars

    On 32-bit Windows, EJOB.JAR is located in:

    drive:Program Files\Easysoft Limited\Easysoft JDBC-ODBC Bridge\Jars
  5. Complete the New JDBC Driver page as follows:
    Property Value
    Name JDBC-ODBC Bridge
    Classname easysoft.sql.jobDriver
    Driver Type Generic
    URL Format jdbc:easysoft://server/dsn:logonuser=myuser:logonpassword=mypassword

    Replace:

    • server with the IP address or hostname of the machine where the JDBC-ODBC Bridge server and Access ODBC driver is installed.
    • dsn with the name of the Access ODBC driver data source.
    • myuser with the name of a Windows user on the JDBC-ODBC Bridge server machine.
    • mypassword with the password for this user.
    Default Translator MSSQL

    Then choose Save Changes.

  6. Choose Databases > Connections.
  7. In the Database Connections page, choose Create New Database Connection...
  8. Choose JDBC-ODBC Bridge when prompted, and then choose Next.
  9. Name the new database connection Microsoft_Access and then choose Create New Database Connection.

The data in your Microsoft Access database is now available to Ignition. For example:

  1. In the Ignition Gateway, choose Launch Designer.
  2. Create a new project.
  3. In the workspace for the new project, add a Table component to a Main Window component.
  4. In the Property Editor window for the Table component, choose the binding icon on the right side of the window.
  5. In the Property Binding window, use the DB Browse feature to choose a table in your Microsoft Access database. Choose OK.

    The data is displayed in the Table component.