how to use ms access file.accdb as a data source

Hey at all,
 
I want to use a MS Access database (.accdb) as a data source for my BIRT report. But I can not get a working connection.
I tried a lot of different Drivers (.jar) to solve this problem. Some of them, which worked for a short time, aren't free and you can't use them anymore after a time or a couple of queries. Some others weren't able to connect to the database.
 
Now I'm trying to use ucanaccess driver. I could create a data source and the test Connection was also successful, but if i want to create a data set, there are no available items from the database for the query...
 
At this Point I'm really desperate.
 
Hope anyone can help me.
 
Thank you very much!!!

Find more posts tagged with

Comments

Clement Wong

From my experience with the ucanaccess driver, you are not able to see the Available Items under the "MS Access Data Source" to browse the objects. I'm not sure why it's behaving this way.
 
However, you can still write a query in the Query Text and have results returned in the Data Set. You will still be able build and run your report.
 
In my example, I used the Northwind sample database.
 
Data Source
Driver Class: net.ucanaccess.jdbc.UcanaccessDriver
Driver URL: jdbc:ucanaccess://C:/temp/northwind.accdb
 
Data Set Query
SELECT * FROM PRODUCTS

jlB_HdF

hi 
I reactivate this topic because I have got the same problem. 
My configuration :
Windows 10 Enterprise 64 bits 
BIRT under Eclipse Neon2 (4.6.2) 
UCanAccess 4.0.0 as jdbc driver 
Access 2016

What I did :
Manage JDBC Drivers :
File Name :ucanaccess-4.0.0.jar 
Original Location : C:\Users\Me\Documents\Driver_jdbc\UCanAccess-4.0.0-bin\ucanaccess-4.0.0.jar
 
Create a new data source :
Driver Class : net.ucanaccess.jdbc.UcanloadDriver 
Database URL : jdbc:ucanaccess://C:/Users/Me/BdD.accdb

The problem :
Test connection : Failed to create a connection. Please verifiy the connection properties
 
The details :
org.eclipse.datatools.connectivity.oda.OdaException: Failed to create a connection. Please verify the connection properties. 
 at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper$TestInProcessDialog$TestConnectionJob$1.run(JDBCSelectionPageHelper.java:218) 
 at org.eclipse.swt.widgets.RunnableLock.run(RunnableLock.java:35) 
 at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Synchronizer.java:182) 
 at org.eclipse.swt.widgets.Display.runAsyncMessages(Display.java:4211) 
 at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3827) 
 at org.eclipse.jface.window.Window.runEventLoop(Window.java:818) 
 at org.eclipse.jface.window.Window.open(Window.java:794) 
 at org.eclipse.jface.dialogs.MessageDialog.open(MessageDialog.java:396) 
 at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper$8.widgetSelected(JDBCSelectionPageHelper.java:1036) 
 at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:249) 
 at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84) 
 at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4418) 
 at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1079) 
 at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4236) 
 at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3824) 
 at org.eclipse.jface.window.Window.runEventLoop(Window.java:818) 
 at org.eclipse.jface.window.Window.open(Window.java:794) 
 at org.eclipse.birt.report.designer.data.ui.actions.NewDataSourceAction.run(NewDataSourceAction.java:127) 
 at org.eclipse.jface.action.Action.runWithEvent(Action.java:473) 
 at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:565) 
 at org.eclipse.jface.action.ActionContributionItem.lambda$4(ActionContributionItem.java:397) 
 at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84) 
 at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4418) 
 at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1079) 
 at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4236) 
 at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3824) 
 at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$4.run(PartRenderingEngine.java:1121) 
 at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:336) 
 at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1022) 
 at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:150) 
 at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:693) 
 at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:336) 
 at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:610) 
 at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:148) 
 at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:138) 
 at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196) 
 at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134) 
 at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104) 
 at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:388) 
 at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:243) 
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) 
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
 at java.lang.reflect.Method.invoke(Unknown Source) 
 at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:673) 
 at org.eclipse.equinox.launcher.Main.basicRun(Main.java:610) 
 at org.eclipse.equinox.launcher.Main.run(Main.java:1519) 
 at org.eclipse.equinox.launcher.Main.main(Main.java:1492)
 
 
Thanks by advance for your help

CodeRookie

I think you need to move the jar file to where your designer is installed. For me, this is the location:
 
C:\Actuate3\BDPro\eclipse\plugins\org.eclipse.birt.report.data.oda.jdbc_4.4.0.v20150206-1039\drivers
 
Hope this helps.
 
Scott

jlB_HdF

A great thank for your advice Scott 
The driver was already in this repertory. Probably when I have used the Manage Driver. 
By security I have also duplicated the sub repertories (lib and loader). 
The result is the same
 
Jean-Luc

pricher

Hi,
 
Try putting the 4 jar files from the ucanaccess lib folder in the same folder where ucanaccess-4.0.0.jar is installed.
 
Hope this helps,
 
P.

jlB_HdF

Hi
 
Unfortunately there is no improvement
 
JL

pricher

Works for me in BIRT 4.6.0. 
 
P.

jlB_HdF

Hi
 
After a new try, I confirm, it is OK !

I thank you Scott and Pierre for your help. 
For everybody who have the same problem :
In the directory
C:\eclipse\plugins\org.eclipse.birt.report.data.oda.jdbc_4.6.0.v201606072122\drivers\
You have to copy the 5 .jar
commons-lang-x.y 
commons-logging-x.y.z 
hsqldb 
jackcess-x.y.z 
ucanaccess-x.y.z

Pierre, do you have any other advices about communication between BIRT UCanAccess and Access ? 
JL