Microsoft Access

Microsoft Access, being part of the Office suite, is a widely used storage format. It uses the Microsoft Jet Database Engine as the storage format for its native tables. Access databases are stored in disk files with an .mdb extension. The disk file stores the entire database (tables, indexes, metadata, and so forth).

In our final testing of the connection to Microsoft Access, we used Office 2003 (thus Access 2003). Earlier tests used Office 2000 (thus Access 2000). The ODBC driver used was version 4.00.6019.00. Both Access 2003 and 2000 use the same Jet Database Engine.

Access Behaviour

Column Default Values

Although Microsoft Access itself supports column default values, this is not supported through the Microsoft Access ODBC driver. When getting column attributes, it will always report the column as having no default value.

Note that defaults are not supported at all through the Access ODBC driver (not supported in the DDL SQL syntax). Even though the DataFlex ODBC Driver allows you to specify a default value for a column (DF_FIELD_DEFAULT_VALUE), this will have no effect.

Column Null-ability

Although Microsoft Access itself supports columns that do not allow null values, this is not supported through the Microsoft Access ODBC driver. When getting column attributes, it will always report the column as allowing null values. If the column attribute required is set in Access, the Access ODBC driver will still report the column as allowing null values.

The SQL syntax supported by the Access ODBC driver does support creating columns that disallow null values. Therefore, you are able to create tables that do not allow null values. When these tables are opened in Database Builder, for example, they will report all columns as allowing null values.

When using Database Builder to edit an Access table definition, make sure to set the Nullable column for every field to the correct value. You cannot trust the values that are reported and must set them to the desired values. Alternatively, you can use Access to edit the table definition. In that case, make sure to delete the table’s cache file.

Database Configuration File

The name of the database configuration file for Microsoft Access is Access.int.

A configuration file with recommended settings is part of the installation. Most settings reported by Access are accurate. In this file, the following settings are made:

Default Value Keywords

As explained in the section on Column Default Values, default values are not supported through ODBC. Setting the default value database configuration keywords has no effect; they are ignored. In the installed Access.int configuration file, these keywords are not set.

Duplicate Record Keywords

The duplicate record keywords are set to the Access duplicate record error state and number as follows:

Duprec_Errornumber -1605
Duprec_State       23000

Max Active Statements

The Max_Active_Statements keyword is set to 0.

Use Identity Type

The Use_Identity_Type keyword is set to 1. This will ensure the ODBC Driver uses the Access AutoNumber type for record identity columns. If you convert a database of an existing DataFlex application to Access, the DataFlex application does not need to be adjusted.

You can only use this setting if the column’s NewValues attribute is set to increment. In Access, you can assign AutoNumbers in two different ways: increment and random. See the Access documentation for more information.

If you set this keyword to 0 (the default value), the AutoNumber type will not be used. This means you will have to adjust existing DataFlex code to assign record identifiers.