Drivers for MS Access databases are often installed, by default, with the Windows OS. The default drivers are built for 32-bit architectures, but that won't be a problem for us since, as pointed out earlier, QlikView can make use of any 32-bit driver.

Connectivity to an MS Access database is provided by Microsoft either through ODBC or OLE DB drivers. At this point, we must decide which of the two types of driver we want to use. Since the connection setup via the OLE DB driver is more straightforward, we will opt for that method. However, we will take a moment to briefly describe the configuration process for ODBC drivers.

How to set up an ODBC connection

In order to create the ODBC configuration, we need to go to the ODBC Data Source Administrator window. Access this window via Control Panel | Administrative Tools | Data Sources (ODBC).

Once in the ODBC Data Source Administrator window, we'll go to the System DSN tab so that the configuration we set is visible for any user of the machine, and then we'll click on Add…, as shown in the following screenshot:

Tip

Accessing a 32-bit ODBC Data Source Administrator from a 64-bit machine

If you are using a 64-bit machine and need to configure a 32-bit ODBC driver, you will need to access ODBC data source administrator from a different location. Go to the %systemdrive%WindowsSysWoW64 folder and launch the Odbcad32.exe file. Otherwise, only 64-bit drivers will be available to configure.

The Create New Data Source window appears, which is where we will be able to select the driver we want to use, as shown in this screenshot:

After clicking on Finish, the configuration window will appear. Depending on the driver you select, the configuration and parameters required for the connection will vary. However, the configurations for almost all drivers on this, and all of the following dialog windows, are very simple, with self-explanatory fields.

After configuration, an ODBC connection can be used from QlikView in the same manner as the OLE DB connection, just by selecting ODBC instead of OLE DB when creating the connection string, which is described in the following section.

Let's now continue with our example using the OLE DB driver. If you followed the process just described, click on Cancel to follow the OLE DB procedure instead.

The connection string is basically a set of instructions and specifications with which QlikView will establish the communication with the database. It contains the database name or network location, the driver name, as well as the credentials with which we will access the database (username and password), if needed.

The connection string is created from QlikView, so the next thing we will do is open the QlikView document Airline Operations.qvw that we created earlier in Chapter 3, Seeing Is Believing. We will add new tables to the data model, this time extracting them from MS Access, to continue exploring how QlikView's built-in extraction capabilities work.

Once the QlikView document is opened, go over to the Edit Script window (Ctrl + E or File | Edit Script…). We've already worked briefly with this window, and this time, we will make use of the Database section in the Data tab.

Activate the Airports tab and position the cursor on the last line, below the existing Load statement. From the Database section, select OLE DB from the drop-down menu, and click on the Connect… button, shown in the following screenshot:

The Data Link Properties window, which is composed of several tabs, will appear. The first tab (Provider) shows a list of all the available OLE DB drivers. Here, we will select Microsoft Jet 4.0 OLE DB Provider. Once selected, click on Next to move to the Connection tab, in which we will specify the database file we want to connect to, as shown in the following screenshot:

Click on the browse (…) button, placed between number 1 and number 2, to select the database file we have stored in the Data FilesMDBs folder.

Since the database file does not require logon credentials, we will leave the User name and Password fields blank. The Blank password checkmark should be selected as well.

We will now click on Test Connection to make sure the connection is established. A message will indicate if the test went well and, if so, we may now click on OK. If not, we need to make sure the configuration is correct and verify that the database file is accessible.

After clicking on OK, you should see the newly generated connection string as part of the script.

It is possible to have several connection strings in the same QlikView document. This allows you to pull data from different sources. Each time a new connection string is found during the script execution, the previous connection is automatically disconnected. We can also use the disconnect; statement to explicitly drop the previous database connection before connecting to the next one.

The SELECT statement is used to pull data from the database into our QlikView document, and tells the DBMS the specific set of data we want. We could just type it manually, but instead we will use the Create Select Statement dialog in order to find the table we want to read as well as the fields we need, and automatically populate the required QlikView script.

Since we've already created the connect statement, we can go ahead and click on the Select button from the Data tab. The Create Select Statement dialog window will appear. This window is used to specify the database, table, and fields we want to load. After we click on OK, the corresponding SELECT statement will be generated.

Let's look at the components of this particular window so that we know what each option does. The following screenshot shows the Create Select Statement dialog window:

We can split the window into three horizontal panes. In the top pane, we specify where the tables we want to read are located. We have a Database drop-down field and an Owner field of this database. We also have three buttons to the right, in this top pane:

In the middle pane, we can choose what will become the core of our SELECT statement: the table and fields we will read.

On the left side of this middle pane, we have several checkmarks that will allow us to filter the list of tables we see to the right. We can select to see only Tables, Views, both, and so on. Once we apply the appropriate filter, we can move on to pick the table we need. Note that the list is alphabetically ordered, so you can type the first letter of the table you want to find, to automatically scroll to the section where the tables whose names begin with the specified letter are listed, and then scroll further down until you find it.

After highlighting the table, we can move on to the next section of this middle pane, which is the list of fields which the selected table contains. In this section, we have an additional option which is the ability to sort the listed fields by Text Order or by the Original Order on which they are stored in the database.

Note that we will always see a star symbol at the top of the list. We can highlight this "wildcard" character if we want to pull all of the fields contained in the table. In case we want to load only a few, but not all of the fields, we can do that by highlighting each of them individually and not with the star symbol.

We can also click on Show Icon for Key Fields, if we want to identify the fields that are defined as key fields in the database.

And finally, we have the bottom pane, which at the same time is divided into several tabs. Let's go through each of them briefly:

Adding the airport tables

Even though the Access database contains all the dimension tables used in the airline operations data model and more, we will only extract the tables corresponding to the origin and destination airports and incorporate them into our application.

Using the Create Select Statement wizard described earlier, create the SELECT statement to extract the Origin Airports table with both the %Origin Airport ID and Origin Airport fields. Make sure to create a script in the form of, column with each field name listed in preceding load for us to manipulate it further, if needed.

The following screenshot shows the configuration we need in the Create Select Statement dialog for this particular example:

The resulting script is as follows:

LOAD '%Origin Airport ID',
  'Origin Airport';
SQL SELECT '%Origin Airport ID',
  'Origin Airport'
FROM 'Origin Airports';

Did you notice how we didn't use the star symbol when selecting the list of fields to retrieve, even when we needed to pull all fields? This is a best practice, to ensure that only the required fields are returned by the query, and no more. Suppose, for example, that a new field is added to the source table. If we used the star symbol to query the database, we would automatically retrieve this new field even when it's not necessary for our data model, wasting valuable bandwidth in the process.

Follow the same process to add the script needed to load the Destination Airports table.