Issue

Macromedia ColdFusion MX 6.1 and MX 7 offer two data source types to connect to Microsoft Access databases. They are labeled "Microsoft Access" and "Microsoft Access with Unicode." The Microsoft Access with Unicode type uses the jadoZoom JDBC driver. The jadoZoom driver is often used as an alternative to the standard "Microsoft Access" data source type for connecting to Microsoft Access.

There are several issues to be aware of when using the Microsoft Access with Unicode driver:

• Inserts and updates fail with the error "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." This occurs because the data fails to truncate to the maximum column size.
• Booleans must be specified as True or False, whereas 1 or 0 can be used with the Microsoft Access data source type.
• Default values are not automatically inserted to columns.

Reason

Most of the problems described above are avoided by creating jadoZoom data sources using the driver type 'other' rather than using the 'Microsoft Access with Unicode' type. The jadoZoom driver is used to create the 'Microsoft Access with Unicode' data sources as well, but the URL will be different than those described in this article.

Solution

The alternate way to set up the jadoZoom driver is to specify 'other' for the driver rather than "Microsoft Access with Unicode driver." This method requires two separate steps. The first is to create an ODBC data source using the ODBC Administrator in Window's Control Panel. Then, create a data source of type 'other' in the ColdFusion MX Administrator that references the ODBC data source.

1. Create an ODBC data source using the Windows ODBC Administrator:
   1. Open the Windows control panel (Start > Settings > Control Panel).
   2. Open the ODBC Administrator - ODBC Data Sources (32 bit).
   3. Select the System DSN tab.
   4. Click Add to create a new ODBC data source.
   5. Choose the Microsoft Access driver type and click finish.
   6. Complete the ODBC DSN setup. If you need assistance creating the data source type, use the Help button.
2. Create a ColdFusion data source of type 'other':
   1. Open the ColdFusion MX Administrator.
   2. Select Data Sources.
   3. Add a new data source name and select 'other' as the driver type and click Add.
   4. Enter jdbc:izmado:DSNName for the JDBC URL. Substitute the ODBC DSN you created in step 1 forDSNName in the URL.
      Note: This is case sensitive
   5. Enter com.inzoom.jdbcado.Driver for the driver class.
      Note: This is case sensitive
   6. The Driver name field should remain blank.
   7. Enter a valid username and password. The default for Access is username=admin and no password.
   8. Click Submit to create the data source.

This type of data source has proven a good alternative to the standard "MS Access" data source.

Products affected