Skip to main content

How to add an ODBC data source

Updated

Adding an ODBC Data Source

ODBC is the acronym for Open Database Connectivity, a Microsoft Universal Data Access standard.  As long as a database provides an ODBC driver, ODBC compliant applications such as DataLinker can connect to the database.  On Windows, most drivers are readily available either from Microsoft or from the database vendor.  On the Macintosh, you may need to purchase a third-party ODBC driver, as well as install the ODBC Manager included with your DataLinker download.  See the Adding ODBC data sources on the Macintosh Platform section below.

The first step in creating an ODBC data source is to create a Data Source Name, or DSN.  On Windows, you do this process using operating system utilities. On a Mac, you can do this with the ODBC manager included with DataLinker.

Once you have created your DSNs using native operating system utilities, you can connect to those DSNs with DataLinker by following these steps:

  1. Choose Data Sources > Add a Data Source from the DataLinker menu.
  2. A small dialog appears asking you what type of data source you would like to add.  Choose “ODBC” and click “Next”
  3. The “Add ODBC Data Source” dialog appears.  These are the fields that you need to specify:
    • Name - Type in a name to refer to your data source in DataLinker. This is distinct from the DSN you create in the operating system.
    • Choose - On Windows, this is a button you can click on to bring up the Windows dialog to choose a DSN. On Mac, this is a dropdown list with your available DSNs.
    • SQL Query - The Structured Query Language (SQL) is the standard language used to query and manipulate data in databases.  You can create a query that pulls data from multiple tables into a single data source.  When you select a table in the Table Name dropdown, DataLinker will automatically create a simple SQL query to retrieve the data from the database, and the query will be entered here.  If you want to be more specific about the data you get, type in your own SQL query.  You can compose your query in another application and simply paste the text in here.
    • Cache Size – Data linker caches a certain number of records in memory for display in the DataLinker palette, and for merging.  This field determines the size of the cache.
    • Run Query - Click this button to retrieve the records with information specified in your SQL query.
    • Table Name - This dropdown lists all the tables that exist in the database you are connecting to.  Choose the table you would like to refer to here.  Choosing a table from here will overwrite any SQL query you may have specified.
    • Text Encoding – The type of encoding that the ODBC driver will use to deliver the data. This is usually defined in your DSN.
    • Key Field - This is where you tell DataLinker which field it should use to uniquely identify each record.  It is important that this field will never change for a given record, and that no two records will share the same key.

More About ODBC Data Sources on the Windows Platform

Many ODBC drivers come installed with Windows or are installed automatically with their host application.  For example, linking to a Microsoft Excel file on Windows requires no special software, and creating a DSN that points to a specific Excel file is a fairly straightforward process.  For information on installing drivers to allow you to create DSNs for other database types, you may need to contact the database vendor.

More About ODBC Data Sources on the Macintosh Platform

DataLinker comes with an open source application called ODBC Manager. It's included as a separate installer. You can use ODBC Manager to create DSNs to access your database.

Eeach database requires its own specific ODBC driver, often included with the database by the database vendor.  For example, FileMaker has an ODBC driver for the Macintosh designed specifically for their database.  Other databases, such as the open source MySql database, might require that you purchase a separate ODBC driver from an outside vendor.  Two such vendors are listed below.  Teacup does not endorse either vendor, but merely provides their names as a reference.

http://www.openlinksw.com/

http://www.actualtechnologies.com/

For more information about using ODBC data sources on the Macintosh, OpenLink has an excellent FAQ on their website.  This FAQ also details how to set up a FileMaker database as an ODBC data source.

http://support.openlinksw.com/support/mac-faq.html

Related articles