Getting started with ODBC on Windows

This document shows an example of using the SQL/R ODBC driver on Windows to create a DSN and access an Eloquence dataset from Microsoft Excel via ODBC.

Screenshots are from Microsoft Office 2010 ® running on Windows 7 ®. The user interface on other product or platform versions may typically look a little different.

Installing the SQL/R ODBC driver

The SQL/R download for Windows platforms includes the ODBC driver as well as SQL/R server and client components. By default, only the ODBC driver will be selected for installation. This is sufficient for a PC client accessing an SQL/R and Eloquence server on another system.

Note that installing only the ODBC driver does not require a license key.

On 64-bit Windows, the installation includes both a 32-bit and a 64-bit ODBC driver to allow using ODBC with 32-bit as well as 64-bit applications. Note that the Control Panel Administrative Tools launches the 64-bit ODBC Data Source Administrator by default. For 32-bit applications like Microsoft Excel, the 32-bit ODBC Data Source Administrator needs to be run, which may be found in C:\WINDOWS\SysWOW64\odbcad32.exe or -for convenience- via the Start Menu shortcut "Start Menu » Programs » SQLR » 32-bit ODBC Administrator".

This is the 64-bit ODBC Administrator window (showing the drivers tab):

Start Menu » Control Panel » System and Security » Administrative Tools » Data Sources (ODBC)

This is the 32-bit ODBC Administrator window (showing the drivers tab):

Start Menu » Programs » SQLR » 32-bit ODBC Administrator

Using Microsoft Excel® for creating a DSN and importing data

For accessing an Eloquence database with Microsoft Excel via ODBC, the "Get external data from Microsoft Query" function may be used. It allows to create a DSN connecting to the SQL/R server and then use the optional Query Wizard to specify table, filter and sort criteria to fetch external data into the worksheet.

The "Choose Data Source" dialog allows creating a new data source:

Choose a name for the File DSN and select the SQL/R ODBC driver:

The "Connect..." button prompts for driver specific parameters:

The data source name (here "ToyDB") needs to match the data source name configured on the SQL/R server system (also see Getting started with SQL/R). You may need to ask your SQL/R server administrator for data source names configured for the desired Eloquence databases. This example uses an SQL/R server running on the same PC, so the target is localhost (or 127.0.0.1).

Choosing the newly created data source will start the Query Wizard:

The first step allows to select dataset and items:

The next step allows to specify optional filter criteria:

The next step allows to specify optional sort order:

The final step allows to choose whether to import the resulting data into the Microsoft Excel worksheet or else preview the results and optionally refine the query within the Microsoft Query program:

Here is the resulting table after importing the query results to Excel:

Note that you can review or modify the query properties (including the ODBC connection string and the SQL statement) via the "Data » Connections" dialog.

This is the "Definition" tab of the "Connection Properties" for above example:

The "Edit Query..." button allows to modify the query in Microsoft Query.