Wednesday, May 5, 2010

Connect to a microsoft database on another server

Accesing a microsoft database on another server also has some pitfalls. You may get the following error message:

java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC
Socket][Microsoft][ODBC Microsoft Access Driver] '(unknown)

Adobe offers the following solution:

This error occurs when permissions are not set properly and you try to use the
ODBC Socket or the drive letter of the mapped drive to the database. Follow the
steps below to avoid the issue:
1.Make sure the user who logged onto the machine
has administrator rights.
2.Set that user to be the logon account for the
ColdFusion MX ODBC Server and ColdFusion MX ODBC Agent services in the Windows
services control panel.
3.Map a drive to the remote database.
4.Create the data
source in the ColdFusion Administrator using the Microsoft Access option, not
ODBC Socket.
5.Set the path to the database using the UNC path, such as the
following example: file://machine_name/databases/test.mdb
If you are using a 64 bit version of coldfusion and you want to access a 32 bit microsoft access database you also need to do the following:

- Read http://coldfusion9.blogspot.com/2010/05/connect-to-local-microsoft-access-32.html
- open C:\Windows\System32\odbcad32.exe
- click file dns
- click add
- select microsoft access driver
- click finish
- click select
- in the database name field enter the full unc path (e.g. file://server/folder/database.mdb)
- click ok
- enter desired datasource name (same name as in the coldfusion administrator datasources)

No comments: