Access databases (mdb) on Windows Server 2019

Tools and Libraries
18

Ok, it took about 30min to try this, and I was able to make an access database work with an Lucee Express Version 5.3.10.97 on my Windows 10 Lap. You need to translate that to your installed server. But this works!!! If you have populated your Lucee installation with some other files, please restart from scratch. Here is a solution and the steps you need to make:

  1. Download Lucee Express Version 5.3.10.97 and unzip it. (If you are installing it to a Windows Server 2019 use the Lucee Installer, but you need to translate to the correct paths/directories of your Lucee installation then. I’m showing it here with the Lucee Express Version).

  2. Download the following OSGI compliant JARs from Maven here:

  1. Place those downloaded files to your pathToYourLucee\lucee-express-5.3.10.97\lib.

Let’s say you have a setup like this:

  • a database named database.accdb with a table named table1 placed at pathToYourLucee\lucee-express-5.3.10.97\database.accdb,
  • a webroot served at pathToYourLucee\lucee-express-5.3.10.97\webapps\ROOT

Then continue with the following steps:

  1. remove all files located inside pathToYourLucee\lucee-express-5.3.10.97\webapps\ROOT

  2. Create an Application.cfc at pathToYourLucee\lucee-express-5.3.10.97\webapps\ROOT\Application.cfc with the following code:

//Application.cfc
component {

	this.Name = "MSAccessExample";
    this.dataBasePath=expandPath("../../") & "database.accdb";
    this.datasources["msAccessDB"] = {
        class: "net.ucanaccess.jdbc.UcanaccessDriver",
        connectionString: "jdbc:ucanaccess:///" & this.dataBasePath
     };
}
  1. Create an index.cfm with the following code at pathToYourLucee\lucee-express-5.3.10.97\webapps\ROOT\index.cfm with the following code:
<!--- index.cfm --->
<cfquery name="myquery" datasource="msAccessDB" >
	select * from table1;
</cfquery>
<cfdump var="#myquery#">
  1. Run the Lucee server from your Lucee Express Version

When you execute the index.cfm you get this:

image

In case you get lots of errors in your console output:

java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: net.ucanaccess.converters.FunctionsAggregate
        at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
 ...
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: net.ucanaccess.converters.FunctionsAggregate
        at org.hsqldb.error.Error.error(Unknown Source)
   ...
WARNING:Function already added: CREATE AGGREGATE FUNCTION last(IN val DECIMAL(100,10), IN flag BOOLEAN, INOUT register  DECIMAL(100,10), INOUT counter INT)   RETURNS  DECIMAL(100,10)  NO SQL  LANGUAGE JAVA   EXTERNAL NAME 'CLASSPATH:net.ucanaccess.converters.FunctionsAggregate.last'

it’s because the the DB makes some sort of issues because of privileges. Didn’t had the time to dig further, but if you change the access DB file to be more permissive for the user running Lucee, this should just work. So on your WIN give the DB file permission for that Lucee user (should be “local service” if you install it on Windows 2019 with the Lucee installer).

Another thing: Just like @Terry_Whitney said, please move to a more convenient database engine ASAP.

2 Likes