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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s