Read MS Access File into CFQuery

Here is code snippet that will read arbitrary Microsoft Access file, and return the result in CFQuery. No Coldfusion data source is required.

	<cfscript>
	//list all the JDBC drivers for diagnosis purpose
	factory = createObject("java","coldfusion.server.ServiceFactory");
	writeDump(factory.dataSourceService.getDrivers());
	fileName = "d:/temp/temp.mdb";
	classLoader = createObject("java","java.lang.Class");
	driver = classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	//connect to MDB using 64-bit java and 64-bit MS Access Driver
	connStr = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & fileName;
	driverManager = createObject("java","java.sql.DriverManager");
	con = driverManager.getConnection(connStr, "", "");
	statementStr = "SELECT * FROM Employee_Table";
	sqlStatement = con.createStatement();
	sqlStatement.execute(statementStr);
	resultSet = sqlStatement.getResultSet();
	qry = createObject("java","coldfusion.sql.QueryTable");
	qry.init(resultSet);
	cfQ = qry.FirstTable();
	con.close();
	//cfQ is the select result in CFQuery
	writeDump(cfQ);
	</cfscript>

view raw ReadMSAccess.cfm hosted with ❤ by GitHub

Environment:
- Coldfusion 9, 64-bit

Notice, in the snippet, there is a dump of all DB drivers loaded by Coldfusion. So, if you ever need access to other dynamic data connection, just swap out the connection string in the sample.