M$Access DB and ColdFusionMX for Solaris

I'm sitting here trying to figure a way to use ColdFusionMX on Solaris8 to get data out of an Microsoft Access Database. I heard a rumor that there was an ODBC driver or some other driver that would allow this to happen. Does anyone know if this is true? Can I store an Access DB File on a Solaris File system and use ColdFusionMX to select data from it which is also running on the same Solaris system? I have a feeling that the answer would be NO, but just wanted to post this to see if I am correct. I searched the CF-TALK list for this and did not find anything, but I could have used the wrong search criteria. If this is indeed not possible, I thought of another idea to accomplish this that might work. Use ColdFusionMX in distributed mode. Install ColdFusionMX and the Access DB file on a Windows2000/XP server and use ODBC to connect to Access. Configure the web server, maybe Apache2, on Sun Solaris8 with the ColdFusionMX distributed client (wsconfig.jar). I wonder if this would work like I expect it to? Anyone already done this? I can already see the benefits. I can segregate my small Access Applications from the rest.  ;-) I strongly invite any and all comments. Thanks, Troy -- ------------------------- Troy Simpson   Applications Analyst/Programmer, MCSE, OCPDBA North Carolina State University Libraries Campus Box 7111 | Raleigh | North Carolina ph.919.515.3855 | fax.919.513.3330 E-mail: Troy_Simpson@ncsu.edu

Good question and I hope you get a lot of response but can you export the data from Access to MySQL or something that runs on UNIX and have CF talk to it instead? I'm sitting here trying to figure a way to use ColdFusionMX on Solaris8 to get data out of an Microsoft Access Database. I heard a rumor that there was an ODBC driver or some other driver that would allow this to happen. Does anyone know if this is true? Can I store an Access DB File on a Solaris File system and use ColdFusionMX to select data from it which is also running on the same Solaris system? I have a feeling that the answer would be NO, but just wanted to post this to see if I am correct. I searched the CF-TALK list for this and did not find anything, but I could have used the wrong search criteria. If this is indeed not possible, I thought of another idea to accomplish this that might work. Use ColdFusionMX in distributed mode. Install ColdFusionMX and the Access DB file on a Windows2000/XP server and use ODBC to connect to Access. Configure the web server, maybe Apache2, on Sun Solaris8 with the ColdFusionMX distributed client (wsconfig.jar). I wonder if this would work like I expect it to? Anyone already done this? I can already see the benefits. I can segregate my small Access Applications from the rest.  ;-) I strongly invite any and all comments. Thanks, Troy -- ------------------------- Troy Simpson   Applications Analyst/Programmer, MCSE, OCPDBA North Carolina State University Libraries Campus Box 7111 | Raleigh | North Carolina ph.919.515.3855 | fax.919.513.3330 E-mail: Troy_Simpson@ncsu.edu

Troy You probably can't do exactly what you want -- run MS-Access native on   a non-windows platform, but you can get close enough. As far as I have been able to determine, the Jet engine (MS-Access   RDBMS) is implemented within the microsoft ODBC driver -- the driver is   the database.  Because of this, there are no implementations except on   windows platforms. But, if you can interface a windows box on a network, or run emulated   windows on Solaris (VMWare?), you can set up CFMX to interface the   MS-Access system (ODBC driver) through TCP/IP. Now, MS-Access doesn't provide TCP/IP access, so you need some   3rd-party software. OpenLink Software provides a package that will do the job. It consists of a JDBC driver for CFMX Server software that runs on windows and interfaces the JDBC driver   (above) A MS-Access agent, that runs on windows and connects the server with   the MS-Access ODBC driver. I have successfully done this with Mac OS X, running MS-Access97/2000   under VirtualPC emulated WindowsNT/XP MS-Access can be running, or not. I used CFMX programs to read the a large MS-Access mdb, validate the   data, normalize, create and populate tables using Sybase_ASE (could be   any CFMX supported db). In my case, this was all running on a single box -- everything but the   emulated  MS-Access ODBC driver, an OpenLink server/agent were run in   Native OS X. I haven't tried Solaris, but the same setup should be possible. The Openlink software is $500 -- which seems a little pricey.  Except   you could easily recover this cost with a single conversion (as   described above). Further, OpenLink Software has a free trial, so you can play before you   pay. Dick On Wednesday, October 23, 2002, at 10:22 AM, Troy Simpson wrote: ----- Excess quoted text cut - see Original Post for more -----

Yea, I could.  ;-) But we are trying to provide a service for people in other departments and they only know MS Access.  Additionally, management wants us to try and host this on the Unix systems if possible. Troy David Adams wrote: ----- Excess quoted text cut - see Original Post for more -----

Hey Troy, Try jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=[path to access mdb] using the jdbc odbc bridge. That's how I access it straight from java. Never tried it with MX but in theory... Rob Yea, I could.  ;-) But we are trying to provide a service for people in other departments and they only know MS Access.  Additionally, management wants us to try and host this on the Unix systems if possible. Troy David Adams wrote: ----- Excess quoted text cut - see Original Post for more -----

You have any more real examples for the jdbc:... string? What *.jar or *.class files do I need? And this will work on Sun Solaris? Thanks, Troy Rob Rohan wrote: ----- Excess quoted text cut - see Original Post for more -----

Ok, if it does work it would work like this: 1) in "Add new data source" setup in cfide choose "other" for "Driver" 2) for jdbc url put jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=[path to access mdb]    where [path to access mdb] is C:\blah\blah.mdb or /usr/blah/blah.mdb 3) Driver class = sun.jdbc.odbc.JdbcOdbcDriver 4) Driver namae = whatever you want If it does work, please let us know. And I don't see why it wouldn't work in Solaris, I think I have used this in Linux (just not with MX). Cheers, Rob You have any more real examples for the jdbc:... string? What *.jar or *.class files do I need? And this will work on Sun Solaris? Thanks, Troy Rob Rohan wrote: > Hey Troy, > > Try > > jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=[path to access mdb] > > using the jdbc odbc bridge. That's how I access it straight from java. Never ----- Excess quoted text cut - see Original Post for more -----

Thanks Rob it this works it would be great. :-) So the Java URL would be: jdbc:odbc:Driver={Microsoft Access Driver(*.mdb)};DBQ=[/usr/db/db1.mdb] or jdbc:odbc:Driver={Microsoft Access Driver(*.mdb)};DBQ=/usr/db/db1.mdb The second one does not have the [,]. The reason I'm asking for examples is because if it is going to work, the syntax is what usually kills it. Thanks, Troy Rob Rohan wrote: ----- Excess quoted text cut - see Original Post for more -----

no [] Good luck Thanks Rob it this works it would be great. :-) So the Java URL would be: jdbc:odbc:Driver={Microsoft Access Driver(*.mdb)};DBQ=[/usr/db/db1.mdb] or jdbc:odbc:Driver={Microsoft Access Driver(*.mdb)};DBQ=/usr/db/db1.mdb The second one does not have the [,]. The reason I'm asking for examples is because if it is going to work, the syntax is what usually kills it. Thanks, Troy Rob Rohan wrote: ----- Excess quoted text cut - see Original Post for more ----- in ----- Excess quoted text cut - see Original Post for more ----- and > > they only know > > MS Access.  Additionally, management wants us to try and host this on the ----- Excess quoted text cut - see Original Post for more ----- Solaris8 > > > to get data out of an Microsoft Access Database. > > > I heard a rumor that there was an ODBC driver or some other driver that ----- Excess quoted text cut - see Original Post for more ----- accomplish ----- Excess quoted text cut - see Original Post for more -----

and you need to have a space Microsoft Access Driver (*.mdb)                        ^--- space! no [] Good luck Thanks Rob it this works it would be great. :-) So the Java URL would be: jdbc:odbc:Driver={Microsoft Access Driver(*.mdb)};DBQ=[/usr/db/db1.mdb] or jdbc:odbc:Driver={Microsoft Access Driver(*.mdb)};DBQ=/usr/db/db1.mdb The second one does not have the [,]. The reason I'm asking for examples is because if it is going to work, the syntax is what usually kills it. Thanks, Troy Rob Rohan wrote: ----- Excess quoted text cut - see Original Post for more ----- in ----- Excess quoted text cut - see Original Post for more ----- and > > they only know > > MS Access.  Additionally, management wants us to try and host this on the ----- Excess quoted text cut - see Original Post for more ----- Solaris8 > > > to get data out of an Microsoft Access Database. > > > I heard a rumor that there was an ODBC driver or some other driver that ----- Excess quoted text cut - see Original Post for more ----- accomplish ----- Excess quoted text cut - see Original Post for more -----

What, no one placing bets on this one? :) ~Todd ----- Excess quoted text cut - see Original Post for more -----

I got back the following error when I was trying to create the M$ Access data source: Connection verification failed for data source: MSAccess []java.sql.SQLException: null The root cause was that: java.sql.SQLException Is this a problem related to being unable to find the *.jar or *.class file for sun.jdbc.odbc.JdbcOdbcDriver? If so, what JAR file do I need in the ClassPath? I'm running ColdFusionMX on Sun Solaris 8 and Apache 2.0.40. Troy Rob Rohan wrote: ----- Excess quoted text cut - see Original Post for more -----

The driver (sun.jdbc.odbc.JdbcOdbcDriver) is in the rt.jar in jdk1.4. I cant find it in the normal MX distro it might be named something else. I am still looking... I got back the following error when I was trying to create the M$ Access data source: Connection verification failed for data source: MSAccess []java.sql.SQLException: null The root cause was that: java.sql.SQLException Is this a problem related to being unable to find the *.jar or *.class file for sun.jdbc.odbc.JdbcOdbcDriver? If so, what JAR file do I need in the ClassPath? I'm running ColdFusionMX on Sun Solaris 8 and Apache 2.0.40. Troy Rob Rohan wrote: ----- Excess quoted text cut - see Original Post for more ----- this ----- Excess quoted text cut - see Original Post for more ----- export > > > > the data from Access to MySQL or something that runs on UNIX and have ----- Excess quoted text cut - see Original Post for more ----- same ----- Excess quoted text cut - see Original Post for more ----- post ----- Excess quoted text cut - see Original Post for more ----- I ----- Excess quoted text cut - see Original Post for more ----- server ----- Excess quoted text cut - see Original Post for more -----

Nope. It's there. Try stopping and starting the CF service and checking connection again, if that doesn't work - I don't know what to tell ya. Sorry. Maybe an MM guy would like to comment? I got back the following error when I was trying to create the M$ Access data source: Connection verification failed for data source: MSAccess []java.sql.SQLException: null The root cause was that: java.sql.SQLException Is this a problem related to being unable to find the *.jar or *.class file for sun.jdbc.odbc.JdbcOdbcDriver? If so, what JAR file do I need in the ClassPath? I'm running ColdFusionMX on Sun Solaris 8 and Apache 2.0.40. Troy Rob Rohan wrote: ----- Excess quoted text cut - see Original Post for more ----- this ----- Excess quoted text cut - see Original Post for more ----- export > > > > the data from Access to MySQL or something that runs on UNIX and have ----- Excess quoted text cut - see Original Post for more ----- same ----- Excess quoted text cut - see Original Post for more ----- post ----- Excess quoted text cut - see Original Post for more ----- I ----- Excess quoted text cut - see Original Post for more ----- server ----- Excess quoted text cut - see Original Post for more -----

By the way, I just tried this on a win box (jre 1.4.0_02) and it worked. I don't have a real computer here at work so I cant check on Linux or Solaris (or OSX for that matter) URL = sun.jdbc.odbc.JdbcOdbcDriver Class = jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\test.mdb Name = test Nope. It's there. Try stopping and starting the CF service and checking connection again, if that doesn't work - I don't know what to tell ya. Sorry. Maybe an MM guy would like to comment? I got back the following error when I was trying to create the M$ Access data source: Connection verification failed for data source: MSAccess []java.sql.SQLException: null The root cause was that: java.sql.SQLException Is this a problem related to being unable to find the *.jar or *.class file for sun.jdbc.odbc.JdbcOdbcDriver? If so, what JAR file do I need in the ClassPath? I'm running ColdFusionMX on Sun Solaris 8 and Apache 2.0.40. Troy Rob Rohan wrote: ----- Excess quoted text cut - see Original Post for more ----- this ----- Excess quoted text cut - see Original Post for more ----- export > > > > the data from Access to MySQL or something that runs on UNIX and have ----- Excess quoted text cut - see Original Post for more ----- same ----- Excess quoted text cut - see Original Post for more ----- post ----- Excess quoted text cut - see Original Post for more ----- I ----- Excess quoted text cut - see Original Post for more ----- server ----- Excess quoted text cut - see Original Post for more -----

Rather: Class = sun.jdbc.odbc.JdbcOdbcDriver URL = jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\test.mdb Name = test By the way, I just tried this on a win box (jre 1.4.0_02) and it worked. I don't have a real computer here at work so I cant check on Linux or Solaris (or OSX for that matter) URL = sun.jdbc.odbc.JdbcOdbcDriver Class = jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\test.mdb Name = test Nope. It's there. Try stopping and starting the CF service and checking connection again, if that doesn't work - I don't know what to tell ya. Sorry. Maybe an MM guy would like to comment? I got back the following error when I was trying to create the M$ Access data source: Connection verification failed for data source: MSAccess []java.sql.SQLException: null The root cause was that: java.sql.SQLException Is this a problem related to being unable to find the *.jar or *.class file for sun.jdbc.odbc.JdbcOdbcDriver? If so, what JAR file do I need in the ClassPath? I'm running ColdFusionMX on Sun Solaris 8 and Apache 2.0.40. Troy Rob Rohan wrote: ----- Excess quoted text cut - see Original Post for more ----- this ----- Excess quoted text cut - see Original Post for more ----- export > > > > the data from Access to MySQL or something that runs on UNIX and have ----- Excess quoted text cut - see Original Post for more ----- same ----- Excess quoted text cut - see Original Post for more ----- post ----- Excess quoted text cut - see Original Post for more ----- I ----- Excess quoted text cut - see Original Post for more ----- server ----- Excess quoted text cut - see Original Post for more -----

I think that Dick might be right about MS Access. The only driver for M$ Access is Microsoft's ODBC Driver which is only for Windows systems. All the other drivers like the JDBC drivers act as a middle layer between CFMX and the Access ODBC driver. I'm afraid that there is not a pure 100% Java JDBC driver for M$ Access. I believe I'm going to be stuck with the OpenLink Software Solution for now. I hate having to introduce another system into the mix. Troy Rob Rohan wrote: ----- Excess quoted text cut - see Original Post for more -----

Actually that does make total sense. You'd have to *have* and ODBC driver for a jdbc odbc *bridge* to work. Sorry, I am retarded. I think that Dick might be right about MS Access. The only driver for M$ Access is Microsoft's ODBC Driver which is only for Windows systems. All the other drivers like the JDBC drivers act as a middle layer between CFMX and the Access ODBC driver. I'm afraid that there is not a pure 100% Java JDBC driver for M$ Access. I believe I'm going to be stuck with the OpenLink Software Solution for now. I hate having to introduce another system into the mix. Troy Rob Rohan wrote: > By the way, I just tried this on a win box (jre 1.4.0_02) and it worked. I > don't have a real computer here at work so I cant check on Linux or Solaris ----- Excess quoted text cut - see Original Post for more ----- file ----- Excess quoted text cut - see Original Post for more ----- the ----- Excess quoted text cut - see Original Post for more ----- /usr/blah/blah.mdb ----- Excess quoted text cut - see Original Post for more ----- access ----- Excess quoted text cut - see Original Post for more ----- java. ----- Excess quoted text cut - see Original Post for more ----- departments ----- Excess quoted text cut - see Original Post for more ----- on ----- Excess quoted text cut - see Original Post for more ----- but ----- Excess quoted text cut - see Original Post for more -----

Troy Are you running the MS-Access & windows portion of the OpenLink package   under emulation or on a real windows box. I run it under emulation and it seems to perform pretty well (better than I expected). Of course it is only used to convert to/from a real RDBMS, and not as a production CFMX datasource. I look at use of OpenLink by CFMX as just an extension of using MS-Access by CFMX (on a win platform) -- you only do it for a little while, and only when you absolutely need to I bought MS-Office, just so I could manipulate client's mdb on win emulation,  now OpenLink extends this ability to CFMX on non-win platforms. Dick On Thursday, October 24, 2002, at 04:25 PM, Troy Simpson wrote: ----- Excess quoted text cut - see Original Post for more -----

I am running MS-Access on a Windows 2000 system and using the Window version of the OpenLink package. CFMX is running on the SunSolaris8 system as a client. I recently posted the details of this earlier under this same subject. I agree with you about not running MS-Access in a production mode.  I discourage it.  But I do have cases where I need to. Troy Dick Applebaum wrote: ----- Excess quoted text cut - see Original Post for more -----

This helped me to understand things about ODBC and JDBC a little better: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/bridge.doc.html Troy Rob Rohan wrote: ----- Excess quoted text cut - see Original Post for more -----

I don't know a lot about MS SQL Server so forgive me for my ignorance. What version of MS SQL Server will manage MS Access files? Is it possible to configure MS SQL Server to serve data from an MS Access file? Thanks, Troy Dick Applebaum wrote: ----- Excess quoted text cut - see Original Post for more -----

Dick, Thanks for the input.  I downloaded the trial software and got it running. How stable has this been for you Dick? For everyone else here is how I did it and to reinterate what Dick said. 1.  I download the 30 day trial software from www.openlinksw.com 2.  Install server software on the Windows 2000 system.  Created an ODBC DSN using Microsoft Access Driver. 3.  Install the client software on the Sun Solaris system.  This software contains a the opljdbc2.jar file that you'll need to include in you ClassPath on the Java and JVM page. 4.  I then created a Data Source of type Other. JDBC URL:   jdbc:openlink://w2k.domain.com:5000/SVT=Odbc/DATABASE=db1/UID=Admin/PWD= Driver Class: openlink.jdbc2.Driver Boom!! Done. Now I can access my Access Database File from ColdFusionMX running on Solaris8. I'm still hopeing that Micosoft or someone will come out with a JDBC/ODBC/Other Driver for Access on Unix. Thank, Troy Dick Applebaum wrote: ----- Excess quoted text cut - see Original Post for more -----

On Thursday, October 24, 2002, at 04:01 PM, Troy Simpson wrote: > I'm still hopeing that Micosoft or someone will come out with a > JDBC/ODBC/Other Driver for Access on Unix. > > If what I am told is true, that is either unlikely, expensive or both. I was  told that the Jet engine (MS-Access) is implemented within the driver -- so to come out with a 'Nix driver would be the same as coming out with MS-Access, itself, on that platform. Apparently, MS has chosen not to do this.  Someone else would likely need to License Access from MS to do this. The OpenLink solution interfaces the MS MS-Access driver with a win-program that acts like a server, accessible thru TCP/IP via JDBC or ODBC, I think what would make most sense is: 1) lots of developers ask (and be prepared to pay)  OpenLink for a lower developer price (say, $200, instead of $500) 2) have the client purchase the package 3) MacroMedia license the package from OpenLink 4) Bite the bullet and buy the $500 package, yourself (as I will do when my trial ends), because it pays for itself, the first time you use it. Dick

Troy, Because Access is a file-based database and not Client/Server, there is no way to directly read an Access database from a non-Windows system.  If there were a driver, it would have to re-implement the entire Jet engine and that is simply not possible (at least not cost effective given that MS has complete control over the proprietary db file format). As you said, using CF in distributed mode with CF server installed on Windows server and linked to Apache on Unix server should work.  Another option is to run CF on a Windows server and provide data to CF running on Apache via web services (CFCs). Other options would be to create a COM based data layer (or Java based I guess) that runs on a Windows box (MTS or COM+) and is called from the Unix box (you can make COM calls from Unix boxes to Windows boxes, right?).  However, with CF's many COM issues, this is probably not the best options. If it's possible to get away from Access and use a more robust database, that would be the best option.  If Oracle or MSSQL are out of the question due to cost or maintenance, what about PostgreSQL or MySQL? My $0.02. Sam Date: Wed, 23 Oct 2002 13:22:51 -0400 From: Troy Simpson <Troy_Simpson@ncsu.edu> Subject: M$Access DB and ColdFusionMX for Solaris Message-ID: <3DB6DAEB.55BD33BE@ncsu.edu> I'm sitting here trying to figure a way to use ColdFusionMX on Solaris8 to get data out of an Microsoft Access Database. (snip) Thanks, Troy

> Subject: Re: M$Access DB and ColdFusionMX for Solaris > ... > Sorry. Maybe an MM guy would like to comment? I'm not an MM guy, but I don't think this is going to work. The JDBC-ODBC bridge will allow you to use ODBC datasources from JDBC; if you don't have an ODBC driver for MS Access on Solaris, the bridge won't do anything for you. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444