Common ODBC Problems

If your ODBC link database fails to open, or opens correctly but fails to save, then the following potential problems should be checked.

General

  1. Check that the ODBC table and column names in the design are correctly spelt. If table and column names are incorrectly spelt, then Microsoft Access will generate an error similar to "Too few parameters. Expected 1".

    If no ODBC column name is supplied in the design, then the Isis field name will be used. Foreign key columns must have the same name as their corresponding primary keys in the ODBC header table.
  2. For reading it is not necessary to link all ODBC columns to fields in the design, however, all primary keys should be linked otherwise Isis may encounter duplicate keys.

ODBC connections on 64-bit Windows

If you are attempting to create and maintain a 32-bit ODBC data source in a 64-bit operating system, then you will need to run odbcad32.exe rather than the Control Panel > Administrative Tools > Data Sources (ODBC) option.

Attempting to use the Data Sources (ODBC) option will result in an "unable to locate driver" message being displayed. This is because the Data Sources (OBDC) option is a 64-bit application, and it does not currently display or support the 32-bit drivers for Microsoft Access etc.

The odbcad32.exe executable is located in your C:\WINDOWS\SysWOW64\ subdirectory.

Problems writing back to ODBC through Isis

  1. Do not link the ODBC foreign keys into the design as they are transparently added by Isis. If present, then the save operation will fail as ODBC will attempt to write to the same column twice.

    For example: In a drilling database, if your primary key ODBC column is HOLEID in the COLLAR table, then the Isis design should only have a HOLEID field in the first COLLAR table, and not in the ASSAY, SURVEY, etc. tables (although "holeid" will exist in the ODBC assay, survey etc. tables).
  2. Check that the ODBC column types match the design field types. For example, linking an ODBC integer field to an Isis text field is acceptable for reading (1234 can be converted to "1234"), but can potentially fail when writing ("ABCD" is not an acceptable integer value).
  3. All columns in the ODBC table should be linked to fields in the design. If some columns are not linked then Isis is unaware of their existence and cannot write data into them. At best this will result in default values being placed into those columns by the ODBC database system (that is, data being lost) and at worst the save will fail.
  4. For Microsoft Access text fields, ensure that the Allow Zero Length property is set to Yes (it is No by default) if there is any chance of having a blank value in that field. This is necessary because Isis does not support the concept of a NULL/EMPTY value, and so such values will, when read from the ODBC database, be translated to zero length strings (that is,, "") which cannot be written back without this setup.
  5. Microsoft Access date/time fields can be linked to Isis text fields, however, they must contain a value to be successfully written since a blank string (that is, "") is not a valid date.

If you have any questions or need help, don't hesitate to contact us.