Access databases (mdb) on Windows Server 2019

Hello (I use google translator),
I finally got Lucee running on a Windows Server 2019 with IIS.
Now I would like to access Access databases (mdb) with Lucee.
Are there instructions for this?
Do I really need ucanaccess?
How do I install ucanaccess?
I downloaded it but there is no setup file, what next?

Do you want to use Lucee with Access as a Desktop app or for use in a website?

We’ve been using Access for years for websites on a Coldfusion 8-9 server. We now want to switch to Lucee. Coldfusion 8-9 supported Access with no problems. Of course, we want to switch to MySQL soon, but due to time and other reasons, we can’t do that anytime soon. Before our website changes, I test Lucee local.

It’s very possible you won’t get many information around, and my personal advice is pretty likely to not be the one you might like.

You won’t find many users with a helpful solution, because access data bases is not what most of us use in combination with a web app engine.As of today, it’s not a good choice. Last time I’ve connected access with a cfengine was with CF 4.0

My personal advice is migrate that DB to another one (e.g. mariaDB or mySQL) in first place. There are lots of tools around and docs that makes that pretty easy. Probably easier than trying to make a data source connection to a modern web app engine like Lucee work.

As far as I remember, you can connect it via ODBC connector. We had ~15 years ago the mysql odbc connector to a access file as proxy.

Good luck (you will need it)

1 Like

I use Access as DB for more than 20 years, it works fine for small sites,
and it is very simple and flexible.
I just cut and paste my MDB file, and do what ever I want with the MDB on local.
In Lucee , you use ODBC driver : Update datasource connection Other - JDBC Driver (deprecated)
I work on Linux, but before I worked on windows.
Set Class and connection string in the declared datasource.
It is good to me to know other users with Ucanaccess driver.

1 Like

@Pierre_Larde You rock!!! Thank you for sharing!!! Can you also post it as code snippet, so @donald can copy paste it more easily?

Class is
net.ucanaccess.jdbc.UcanaccessDriver

Connection string is
jdbc:ucanaccess:///var/www/C002/database/mydb.mdb (but this is under Linux)
Under windows, I can’t remerber well
It should be something like
jdbc:ucanaccess:C:/inetpub/www/C002/database/mydb.mdb

Thank you, but don’t I have to install ucanaccess somehow? There is no installer on Windows. Was ucanaccess originally included with Lucee?

Donald, while I’m not at a computer to confirm things for you, my understanding is that you can either extract the zip offered at that site, to place its jars into the lucee classpath (which depends on how you install lucee), or there’s been a lucee extension for it.

For more on each, see resources such as:

https://bitbucket.org/michaelborn_me/ucanaccess/src/master/

Let us know whether either works for you.

I do not remember, how I did to install under Windows.
Anyway, the String should be (windows address)
connectionString: "jdbc:ucanaccess:///C:\Users\me\my\AccessDatabase.mdb
I find this in my docs.

Sure you have to install Ucanaccess,
Or put the right files at the right place in Lucee

I used UCanAccess-5.0.0-bin.zip
5 files are needed
See image file attached and zip file
ucanaccess_jar
UCanAccess-5.0.0-bin.zip (3.2 MB)

Ty,

I used carehart’s rex file from this url Bitbucket

Now I can also select ucanaccess and, to be on the safe side, I still copied the jar files to the lib folder.

ucanaccess

Under Database I entered the following:
jdbc:ucanaccess:///C:\inetpub\wwwroot\finder\database\finder.mdb
ucanaccess2

and get the following error message when I click on “new”:

Donald, a couple of things.

First, the file you refer to would not be “mine”. I pointed to two resources on the topic, and you’re referring first to one of them, from Michael Born. And while you refer to a “rex” file, I assume you mean the “lex” file, for that ucanaccess Lucee Extension Michael created.

Second, you say you “still copied the jar files to the lib folder”. That’s NOT what you should have done. I pointed to the TWO different options, from which to pick, saying clearly to do either one or the other. That other was an answer offered here in the forums by Milan (in 2016), which entailed extracting those zips. By doing both, you may be causing a conflict between the two approaches trying to work, tripping over different java classes.

So for now, since you’ve added the extension, please remove those you added (it will be good for you if the screenshot shows what you put there). My assumption is that the extension would NOT have put any files there, that you would now be removing, and instead it loads its java classes from a place managed by the Lucee extension mechanism.

Note that you should then restart Lucee (to cause it to load jars newly, and remove any such java classloading conflicts). Let us know how that goes.

I removed the jar files again and restarted the server. Unfortunately, the error message is exactly the same.

Could you please post the complete stack trace? Looks like it’s cut off at the bottom (61more).

I don’t know where to find the full error report. But I took a look at the application.log in the hope that there was more, but it says exactly the same thing:

“ERROR”,“http-nio-8888-exec-1”,“01/25/2023”,“16:26:12”,“webadmin5.3.9.166”,"com/healthmarketscience/jackcess/util/ErrorHandler;lucee.runtime.exp.NativeException: com/healthmarketscience/jackcess/util/ErrorHandler
at java.lang.Class.getDeclaredConstructors0(Native Method)
at java.lang.Class.privateGetDeclaredConstructors(Unknown Source)
at java.lang.Class.getConstructor0(Unknown Source)
at java.lang.Class.getConstructor(Unknown Source)
at lucee.commons.lang.ClassUtil.newInstance(ClassUtil.java:950)
at lucee.runtime.db.DataSourceSupport._initializeDriver(DataSourceSupport.java:172)
at lucee.runtime.db.DataSourceSupport.initialize(DataSourceSupport.java:164)
at lucee.runtime.db.DataSourceSupport.getConnection(DataSourceSupport.java:103)
at lucee.runtime.tag.Admin._doVerifyDatasource(Admin.java:2936)
at lucee.runtime.tag.Admin.doUpdateDatasource(Admin.java:2712)
at lucee.runtime.tag.Admin._doStartTag(Admin.java:742)
at lucee.runtime.tag.Admin.doStartTag(Admin.java:355)
at services_datasource_create_cfm1334$cf.call(/admin/services.datasource.create.cfm:88)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1054)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:946)
at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:927)
at services_datasource_cfm414$cf.call(/admin/services.datasource.cfm:108)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1054)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:946)
at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:927)
at web_cfm$cf.call(/admin/web.cfm:492)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1054)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:946)
at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:927)
at server_cfm$cf.call(/admin/server.cfm:2)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1054)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:946)
at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:219)
at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44)
at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2490)
at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2476)
at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2447)
at lucee.runtime.engine.Request.exe(Request.java:45)
at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1198)
at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1144)
at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97)
at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:779)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at mod_cfml.core.invoke(core.java:180)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1789)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.NoClassDefFoundError: com/healthmarketscience/jackcess/util/ErrorHandler
… 61 more
Caused by: java.lang.ClassNotFoundException: com.healthmarketscience.jackcess.util.ErrorHandler not found by ucanaccess.4.0.4 [50]
at org.apache.felix.framework.BundleWiringImpl.findClassOrResourceByDelegation(BundleWiringImpl.java:1565)
at org.apache.felix.framework.BundleWiringImpl.access$300(BundleWiringImpl.java:78)
at org.apache.felix.framework.BundleWiringImpl$BundleClassLoader.loadClass(BundleWiringImpl.java:1950)
at java.lang.ClassLoader.loadClass(Unknown Source)
… 61 more
"

1, You are better off converting your access DB to MS sql express, which will import all your data into the tables / databases and have far better performance than access plus lucee supports MSSQL
2. You will need to install the ODBC drivers for your windows version, and you will need to install microsoft office Unable to use the Access ODBC, OLEDB or DAO interfaces outside Office Click-to-Run applications - Office | Microsoft Learn
3. Lastly, you can follow this tutorial if you cant JUST LIVE without access, it is the JAVA connector how to, which you will have to wrap cfcode around
Java JDBC Example Connect to Microsoft Access Database

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.

1 Like