QlikView Expressor: Reading from an Access Database

    Originally published on 07-21-2011 09:33 AM

     

    Expressor Studio includes a set of supported database drivers (IBM DB2, Informix, Microsoft SQL Server, MySQL Community Edition, MySQL Enterprise Edition, Netezza, Oracle, PostgreSQL, Sybase ASE, and Teradata) that you can use to set up a database connection.

    You may, however, have ODBC drivers, for example, the Microsoft Access Driver (version 12), that are not included in the Expressor Studio installation but which you would like to use to read data. expressor Studio supports this use case. In order to use an ODBC driver, you must first create an ODBC Data Source using the Windows ODBC Data Source Administrator.

    • Start the data source administrator by selecting Start > All Programs > expressor > expressor3 > system tools > Data Sources (ODBC). This insures that the proper version (32 bit) of the data source administrator is utilized.
    • Use the data source administrator to create an ODBC Data Source entry targeted to the desired Microsoft Access database.

    Now you are ready to create the connection.

    1. Start the database connection wizard. You have several ways to begin this process.
      • On the Home tab of the ribbon bar, click New Connection > Database Connection in the Artifacts grouping.
      • In the Tasks panel, select the Connections pane and click on the New Database Connection link.
      • In the Explorer panel, right-click on the project or library and select New Connection > Database Connection from the popup menu.
      • In the Explorer panel, expand the project or library in which you want to create the connection, right-click on the Connections sub-folder, and select New > Database Connection from the popup menu
    2. In the wizard's first window, select the Create a connection for an existing DSN radio button.
    3. In the wizard's second window, select the DSN from the listing. For an Access database, there is no need to enter credentials.
    4. Click Next and in the last window confirm the location  to which you want to save the connection and give the connection a  meaningful name. Click Finish.
      • An entry for the connection will appear under the Connections sub-folder.

    Use this connection when creating a table schema. Select the [Default] entry as the database schema and the desired database table or tables.

     

    *************

     

    February 10, 2014

     

    If you have a 64 bit version of Windows with the 64 bit version of Microsoft Office/Access, then the 32 bit Access ODBC driver (*.mdb, *.accdb) that you must use with Expressor will not be installed.  To install this required driver, download the 2007 Office System Driver: Data Connectivity Components (http://www.microsoft.com/en-us/download/details.aspx?id=23734) and install onto your 64 bit computer.

     

    To create an ODBC DSN, be certain to use the 32 bit version of the ODBC Administrator.  To start this utility, select the Start > All Programs > QlikView > expressor 3 > system tools > Data Sources(ODBC) menu item.