Setting Up an MS Access 32 bit Data Source in ColdFusion

If you see this error when verifying an MS Access data source in ColdFusion Administrator:

Connection verification failed for data source: <data-source-name> java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]The specified data source is not defined.

The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]The specified data source is not defined.

Then you need to follow these steps to properly set up an MS Access 32 bit Data Source on a 64 bit ColdFusion 10 installation running on Windows 2008 SP2 (because the add new data source function in ColdFusion 10 Admin doesn’t do correctly):

  1. Preparations
  2. Set up the 32 bit System DSN
  3. Create the Data Source in ColdFusion Administrator
  4. Verify Connection to Data Source

1 Preparations

Before we get started, here are some tips to ensure a successful setup:

  • If the Microsoft Access driver is not available, then please download and install it:
    • Copy this file to the server where ColdFusion is installed: AccessDatabaseEngine_x64.exe
    • Launch to install the driver using default location and settings
  • If the MS Access data source exists in ColdFusion Administrator, please remove it before creating it via the 32 bit ODBC Data Source Administrator
  • You will see following errors when the db subfolder in the ColdFusion installation is missing:
    • If data source deletion in ColdFusion Administrator results in a 500 Internal Server Error
    • java.io.FileNotFoundExcetption and file operation write error:
      An error occurred when performing a file operation WRITE on file C:\ColdFusion10\<instance-name>\db\slserver54\admin\<data-source-name>.inp
      The cause of this exception was: java.io.FileNotFoundExcetption: C:\ColdFusion10\<instance-name>\db\slserver54\admin\<data-source-name>.inp (The system cannot find the path specified).
    • If the db folder is missing, then copy the db folder into C:\ColdFusion10\<instance-name>. At the very least, you will need this subfolder and its contents: C:\ColdFusion10\<instance-name>\db\slserver54
    • If you have multiple ColdFusion instances, then a good place to look for a copy of the db folder is under the default cfusion instance, i.e. C:\ColdFusion10\cfusion\

2 Set up the 32 bit System DSN

To setup the 32 bit MS Access System DSN, follow these steps:

  • Log on to the server where ColdFusion is installed
  • Browse to C:\Windows\SysWOW64 and locate the 32 bit ODBC Data Source Administrator, i.e. odbcad32.exe
  • Right click this file and launch it as an administrator
  • In the ODBC Data Source Administrator dialogue, make the following selections:
    • Select the System DSN tab and click Add…
    • Select Microsoft Access Driver (*.mdb) from the drivers list and click Finish
    • In the Data Source Name input box, enter the exact data source name to be used in the ColdFusion Administrator (the name here must match)
    • Under Database, click Select… and browse to select the Microsoft Access database file, i.e. database-name.mdb
    • Click OK to close the dialogue
    • You should now see this new data source in the System DSN list
  • Confirm an entry has been added to the registry in the 32 bit node as follows:
    • Click Start and type regedit in the search box and then press Enter
    • Browse to HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > ODBC
    • Expand ODBC.INI node and you should see the new data source listed here

3 Create the Data Source in ColdFusion Administrator

Follow these steps to create the Microsoft Access data source in the ColdFusion Administrator:

  • Log on to the ColdFusion Administrator
  • Click Data Sources under the Data & Services section on the left pane
  • Check to see if this data source already exists. If it does, and there isn’t a registry entry for it, then delete it first before continuing.
  • In the Add New Data Source section at the top, enter and select the following:
    • Data Source Name (this must be the same name used when creating the 32 bit System DSN)
    • Driver: select Microsoft Access
    • Click Add
  • For Database File, enter the full path and file name of the Microsoft Access database (or browse to select it from the file system)
  • Leave Use Default User Name checked
  • Click Show Advanced Settings and make the following changes:
    • For Allowed SQL, de-select the following:
      • CREATE
      • DROP
      • ALTER
      • GRANT
      • REVOKE
    • Tick Enable connection validation
  • Click Submit to create this new data source
  • You will see the following error messages:
    Unable to update the NT registry.
    Variable DRIVERPATH is undefined.
  • Ignore these error messages. An entry in the registry would have been created.
  • To confirm an entry has been added to the registry in the 64 bit node, do this:
    • Click Start and type regedit in the search box and press Enter (if not already opened)
    • Browse to HKEY_LOCAL_MACHINE > SOFTWARE > ODBC
    • Expand ODBC.INI node and you should see the new data source listed here
  • Return to the ColdFusion Administrator and click Data Sources under the Data & Services section on the left pane
  • Find the new data source that you have just created, and click the tick icon to the left of the data source name to verify it.
  • You should see OK in the status column (right most) if everything is good.

4 Verify Connection to Data Source

As a final check to ensure that we can access this new data source, let’s do the following:

  • Create a ColdFusion file (.cfm) and enter the following:
    <!doctype html>
    <html>
    <head>
       <meta charset="utf-8">
       <title>MS Access Datasource Check</title>
    </head>
    <body>
       <h1>MS Access Datasource Check</h1>
       <cfquery name="qCheckDS" datasource="data-source-name">
          SELECT now() AS dt
       </cfquery>
       <cfdump var="#qCheckDS#" label="qCheckDS">
    </body>
    </html>
    
  • Save it somewhere on your website and browse to it.
  • If data source connection is successful, you will see this dump, that is, something similar to this:

Congratulations! You’ve just successfully created an MS Access 32-bit data source on a 64-bit ColdFusion 10 installation.

Setting up an Oracle Thin Client Data Source in ColdFusion

To set up an Oracle thin client data source in ColdFusion 10 on a Windows environment, do the following:

  • Browse to http://www.oracle.com/technetwork/topics/winx64soft-089540.html
    (Note: You will need to create an Oracle account to download if you don’t have one)
  • Download the Oracle Instant Client, i.e. instantclient-basic-win-x86-64-10.2.0.2.0-20060503 from the Oracle website
  • Unzip and copy ojdbc14.jar to C:\ColdFusion10\<instance-name>\wwwroot\WEB-INF\lib, e.g. C:\ColdFusion10\cfusion\wwwroot\WEB-INF\lib
  • Add an entry to the Path system variables
  • Access the System Properties, i.e. Computer > right click and select Properties
  • Click Advanced system settings
  • Click Advanced tab
  • Click Environment variables…
  • Select path under System variables
  • Click Edit…
  • Append C:\ColdFusion10 to Path using the semicolon as a separator, i.e. add this:
    ;C:\ColdFusion10
  • Restart the server (needed when the Path system variables is updated so applications will pick it up)

In the image above, the path to append to the Path System Variable should be:
C:\ColdFusion10

Once the driver is installed and the server rebooted, do the following to create the Oracle thin client data source in ColdFusion 10:

  • Launch the ColdFusion admin via a web browser
  • Click Data Sources under Data & Services section
  • In the Add New Data Source section, enter the data source name and choose Other for the driver selection
  • Click Add
  • Then enter the following details for this new Oracle thin client data source:
    • JDBC URL
    • Driver Class: oracle.jdbc.OracleDriver
    • Driver Name (can be same as driver class)
    • Username (if required)
    • Password (if required)
    • Description (if required)
  • Click Show Advanced Settings
  • Make changes as required, e.g. select the Allowed SQL, set Login Timeout, etc.
  • Click Submit to create the data source
  • If there are no errors, a mesage is displayed to indicate that the data source was successfully created

data sources

Specify the JDBC URL using this format: jdbc:oracle:thin:@host-name:port:sid

If you do not install the Oracle thin client driver first and try to create a new Oracle thin client data source in ColdFusion, then verifying this new data source will result in this error:

Connection verification failed for data source: <data source name>
java.sql.SQLException: No suitable driver available for <data source name>, please check the driver setting in the resources file, error: oracle.jdbc.OracleDriver.

The root cause was that: java.sql.SQLException: No suitable driver available for <data source name>, please check the driver setting in the resources file, error: oracle.jdbc.OracleDriver.