1. Home
  2. Importing and Exporting Data
  3. ODBC Databases
  4. ODBC Databases Overview
Searching...

ODBC Databases Overview

ODBC Databases Open DataBase Connectivity (ODBC) is a standard developed by Microsoft to allow a common method of accessing a variety of databases. The database vendors are required to produce an ODBC driver which connects any program using the ODBC interface to their database. Thus to read a database via ODBC you require an ODBC driver for your database type. The standard ODBC database drivers supplied with Windows includes the following:

  • Microsoft Access Driver (*.mdb)
  • Microsoft dBase Driver (*.dbf)
  • Microsoft Excel Driver (*.xls)
  • Microsoft ODBC for Oracle Driver
  • Microsoft Paradox Driver (*.db)
  • Microsoft Text Driver (*.txt, *.csv)
  • Microsoft Visual FoxPro Driver
  • SQL Server

If you are using a 64-bit version of Genstat and want to connect to a 32 bit ODBC driver (e.g. Microsoft Office Access) then read this help page: Using ODBC with 64bit Windows

Using ODBC, a database is accessed by setting up a Data Source. A Data Source contains the complete information required for accessing the database including the driver, driver specific parameters, database name and location, and user information (e.g. password). To set up a Data Source you can either use the ODBC Data Source Administration panel, which can be accessed in the Control Panel or alternatively this can be done when you invoke ODBC from Genstat. There are three types of Data Sources (DSN – Data Source Name) that can be used to access a database:

User DSN Creates a data source that is specific to the user on the PC and cannot be used by other users.
System DSN Creates a data source that can be used by all users on a system or PC.
File DSN Creates a data source that can be used by any user who can access the file containing the data source information.

The following example shows how an ODBC connection can be set to a Microsoft Access database file from Genstat.

  1. When ODBC is invoked from Genstat you will first be prompted with a dialog where you can specify or create a data source. To create a File Data Source Name select the File Data Source tab then click New.

  2. This will open a dialog where you can select the driver for the connection, in this case select the driver for Microsoft Access driver and click Next.

  3. In the next dialog enter a filename for the DSN or browse to use an existing one then click Continue.
  4. The next dialog displays a summary of the connection. If the information is correct then click Finish, otherwise click Back to go back and change any incorrect information.
  5. The next dialog is specific to the driver that you are using. The dialog shown below is for Microsoft Access here the name and location of the database file needs to be supplied. To do this click Select and browse for the database file (.mdb). Once this has been selected click OK to complete the process.

  6. The new DSN should now appear in the list of File Data Source Names. This can now be selected and will be remembered for future sessions.

Factor labels from Access

In Access a column has a description, and this can be used to apply levels or labels to ordinal groups, or to give the order of the groups if the column already contains levels or labels with its rows. The following figure shows the description being used to assign labels for factor columns.

Note if a description starts with !, then the column will be read in as a factor. Also any labels in the description not already in the factor will be added to the factors groups.

If the description does not start with a !, then it will be used as the columns description within Genstat.

See also

Updated on July 11, 2019

Was this article helpful?

Yes No