Installing and Configuring ColdFusion Server
Managing Data Sources

Connecting to Microsoft Access Databases

ColdFusion lets you access Microsoft Access databases on Windows NT, using ODBC and OLE DB drivers. Microsoft Access is a desktop database, and is not intended or designed for high-volume, multiuser use.

For more information about using Microsoft Access in your application, see the Knowledge Base article #564, "Using Microsoft Access Databases in a Production Environment."For information on securing Access and other file-based databases, such as dBASE, FoxPro, and Paradox, see Knowledge Base article #10953, "http://www.coldfusion.com/Handlers/index.cfm?ID=10953&Method=FullSecurity Best Practice: Securing File-based Databases./a"

Configuring Microsoft Access Options (Windows)

If you install ColdFusion Server on a Windows NT server, you can configure a Microsoft Access database as a ColdFusion data source using an ODBC or an OLE DB driver. For information about using OLE DB drivers with ColdFusion data sources, see Advanced ColdFusion Server Administration.

ODBC: Microsoft Access options (Windows)

The following table describes ColdFusion ODBC options for Microsoft Access data sources. You set these options when you configure a ColdFusion data source.

Option	Description
Data Source Name	A name for your data source.
Description	Descriptive information about the data source.
Database File	Click Browse to select a database file for a file-based ODBC data source.
System Database	Click Browse to select a shared database. The default user is Admin and the default password is an empty string. A system database can be used to secure Microsoft Access database files.
Driver Settings	Page Timeout  The number of milliseconds before a request for a ColdFusion page times out. Buffer Size  The total number of bytes that ColdFusion uses to cache application pages. Enter a value to optimize ColdFusion performance.
Default Login	A username and password combination that ColdFusion uses to access the data source. If your ODBC data source requires a username or password, enter them here. To verify your data source, you need to enter login information here.

Tips for connecting to Microsoft Access (Windows)

Whether you use OLE DB or ODBC connections, the following suggestions can improve your Access database connectivity:

• In the ColdFusion Administrator:
  • Select Server Settings > Settings and set Limit Simultaneous Requests to 5-7 per processor.
  • Select Data Sources > ODBC Data Sources and modify the following ColdFusion settings for each ODBC Access data source:

    Buffer Size = 0 (ODBC only)

    Page Timeout = 600 (ODBC only)

    Clear the Maintain Database Connections check box (located on the CF Settings page)

• Install the latest ODBC or OLE DB Drivers from Microsoft on your ColdFusion Server. You can find these drivers at http://www.microsoft.com/data/.
• Cycle the ColdFusion services each night using the \cfusion\bin\cycle.bat file.
• If you receive an Operation must use an updateable query error message when you attempt to run a ColdFusion template that updates or deletes from an Access database, see Knowledge Base Article #11928, "Operation must use an updateable query error when trying to update Access database/a."
• If you get an access denied error when writing to Access (or any other file-based database), try to recycle the ColdFusion services or clear the Maintain Database Connection option in the Data Source section in the ColdFusion Administrator.

Connecting to Remote Access Data Sources

When you develop your ColdFusion application, your Access data source could be a local one. In many cases, your application might need to connect to data sources that are not local. This section addresses some concerns for remote connections.

Connecting to network data sources from standalone servers

If you have Windows NT machines set up as standalone servers rather than as members of a domain, accessing a remote data source (on one of the standalone servers from a standalone ColdFusion machine over a "direct" connection) can be difficult.

By default in Windows NT, you run ColdFusion under the System Account. However, if you try to connect to a file-based database over the network using a System Account, then you get insufficient permission errors. You must run ColdFusion Application Server under a Domain User Account that has permission to run ColdFusion and has permission to read and write to the mdb file.

The following steps describe how you can set up ColdFusion to access the remote data source.

To set up a remote Access data source for a standalone server:

1. Create a Windows NT Domain User Account for ColdFusion. Set a username, such as cfusion and assign a password, such as bob. Assign the account at least administrative privileges.
2. Create an account for ColdFusion on the remote standalone machine. Set the username to cfusion and the password to bob (the username and password MUST be the same on both participating machines).
3. Assign the account full privileges on the shared directory where the database resides.
4. Log off the Windows NT machine and log on to the domain as username: cfusion password: bob.
5. Select Control Panel > Services > Cold Fusion Application Server and stop the ColdFusion service.
6. Open Windows NT Explorer and select Tools > Map Network Drive.
7. In the Drive box, scroll to the bottom and select (none).
8. In the Path box, select the mapped drive where the database resides. If it does not appear, enter \\YourMachineName\YourSharedDirectoryName and click OK.
   

   Note For an Access database, point to the mdb file. For other file-based databases, such as dBASE/FoxPro, point to the directory that contains the database files.

   
   
9. Select Control Panel > Services > Cold Fusion Application Server > StartUp > LogOnAs > This Account and specify username: YourDomainName\cfusion and password: bob. Click OK.
10. Stop and restart the Cold Fusion service so the changes take effect (the Cold Fusion service now runs under the cfusion account).
11. Open the ColdFusion Administrator.
12. Set up an ODBC data source pointing to the remote database. In the Database File field of the Create Data Source page, type-do not browse to-the name of your database in Universal Naming Convention (UNC) format.
    

    Note The UNC format is: \\servername\sharename\path\filename, where servername is the name of the server that you will connect to, sharename is the name of a volume on that server, path is the name of the directory and subdirectories that contain the file, and filename is the name of the database file; for example, \\mstillman\cdrive\cfusion\database\cfexamples.mdb.

    
    
13. Check if you can connect to the data source with MSQuery (the Microsoft Query tool that uses ODBC the same way as does Cold Fusion).

With these settings, you can verify that your permissions are sufficient to access the remote data source.

Copyright © 2001, Macromedia Inc. All rights reserved.

---