Query MS Access Database

[Former Community Member]

OK - I have a customer that wants to write out to an MS Access Database from Livecycle.  I know this is BAD but they have their reasons - i.e. they have a sever shortage of skills for anything more complex.

Firstly - I created a Java program to test the JDBC ODBC connection - this worked fine - ran queries and updated OK (code is attached)

Howver I have tried connecting to Access from within Livecycle in two different ways - both are failing

Firstly - using the JNDI setup for JDBC ODBC e.g.

<datasources>
  <local-tx-datasource>
    <jndi-name>MSAccess_DS</jndi-name>
    <!-- format of URL is "jdbc:odbc:DSNNAME" -->
    <connection-url>jdbc:odbc:DBFormData</connection-url>
    <driver-class>sun.jdbc.odbc.JdbcOdbcDriver</driver-class>
    <user-name></user-name>
    <password></password>
  </local-tx-datasource>

</datasources>

Calling this within a Livecycle process does not return an error to the process, but the log generates an error:

2009-08-29 23:01:30,768 WARN  [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: General error)

I also tried creating a Java Component and importing it - by creating a very basic jar and importing it to livecycle (based on the same code I used in my Java testing)

It also gave me the general error - and I can see it is happening on the line:

        Connection con = DriverManager.getConnection(dbURL, "","");

Any ideas on what is going wrong, or a workaround?  (Assuming that I have to use Access)

Thanks

Tim

[chetanm_oct]

Can you post the complete stacktrace when you tried using a custom DSC. Use err.printStackTrace in your test.java

[Former Community Member]

Sure - Stack trace attached (from Custom Component)

Does this shed any light??

[chetanm_oct]

Cannot make much from the stacktrace.

• Is the Access and JBoss are running on same system
• If yes then under same user account

[Former Community Member]

Access and JBoss are both running on the same server

The ODBC connection to Access is setup as a System DSN - which is supposed to be accessible to all users including NT Services.

Is there same way that JBoss wouldn't have access to the System DSN???

[Former Community Member]

OK I solved this.  This was only happening on the Win2K3 server - not on my local dev instance

On the server I set the JBoss service to run under the local admin account (in the Services Panel) - after a service restart the jdbc odbc connection worked fine.  It must have been some permissions with the system account