Skip to Content
Author's profile photo Former Member

Connecting to MS Access using receiver JDBC Adapter (Without DSN)

There were couple of questions in the forum, asking as to how do we connect to an MSAccess Database using the JDBC adapter. In this blog, i will explain as to how to connect to a MSAccess DB using receiver JDBC adapter. As a blog has already been written on ‘FILE to JDBC Adapter using SAP XI 3.0’ by Mr.Anirban Ghatak, this blog builds on it and just explains the part of configuring the receiver JDBC Adapter. Steps 1) Create a blank database in Microsoft Access, lets call it ‘TestDB.mdb’. I saved this mdb file in my XI server path itself,xiserverxitempdbTestDB.mdb (you can save it anywhere provided it has a shared access)  2) Add a table in TestDB.mdb, call it TEST_EMPLOYEE with the following rows and save it.  EMPID type text EMPNAME type text EMPAGE type number FLAG type text  3) Now follow the same steps as mentioned in the blog,FILE to JDBC Adapter using SAP XI 3.0except for the receiver jdbc adapter configuration.  4) In the receiver JDBC adapter configuration pass the following parameters,  Transport Protocol: JDBC 2.0 Message Protocol: XML SQL Format Adapter Engine: Integration Server JDBC Driver: jdbc.odbc.JdbcOdbcDriverConnection: jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=//xiserver/xitemp/db/TestDB.mdb 5) In my case there is no userid and password required, but if you have userid and password, then the parameter for Connection becomes,jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=//xiserver/xitemp/db/TestDB.mdb;Uid=yourid;Pwd=yourpwd;image Hope this blog was off some help.

Assigned Tags

      14 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      Hi Sameer, very useful weblog, was struggling to connect to Access using XI. Thanks for the info.
      Author's profile photo Former Member
      Former Member
      hi
      thanks and good to know it was off help

      sameer

      Author's profile photo Former Member
      Former Member
      hi sameer,
                 i have using JDBC to JDBC driver.in sender communication channel i have facing problem.Please help.

      Error during database connection to the database URL 'jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=/home/iqxadm/xifiles/mohittest/sender/Student_Database.mdb' using the JDBC driver 'sun.jdbc.odbc.JdbcOdbcDriver': 'com.sap.aii.adapter.jdbc.sql.DriverManagerException: Cannot establish connection to URL 'jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=/home/iqxadm/xifiles/mohittest/sender/Student_Database.mdb': java.lang.NullPointerException'

      Author's profile photo Former Member
      Former Member
      Very useful article, thanks.

      A question: what JDBC driver can be used for MS Access?

      Author's profile photo Former Member
      Former Member
      jdbc.odbc.JdbcOdbcDriver

      Thanks Sameer

      Author's profile photo Anil Veepuri
      Anil Veepuri
      Hi,

      In my case, the mdb file is on a different host which is at a different location. in that case how the value for the DBQ should be given?

      Regards
      Anil Veepuri

      Author's profile photo Former Member
      Former Member
      Good Work Sameer.
      But had you added some common errors encountered in this scenario and their solution, it would have beem much more helpful.

      Thanks,
      Puneet

      Author's profile photo Former Member
      Former Member
      Hi,

      I am also using the same JDBC driver to run my scenario but i was unable to connect to the MS Acess as i am facing the error like,
      Error during database connection to the database URL 'jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=//INLD50044701A/C/XI/Workspace/xi_input.mdb' using the JDBC driver 'jdbc.odbc.JdbcOdbcDriver': 'com.sap.aii.adapter.jdbc.sql.DriverManagerException: Cannot establish connection to URL 'jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=//INLD50044701A/C/XI/Workspace/xi_input.mdb': SQLException: [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

      Can you please help me out in this regard.

      Thanks,
      Soorya

      Author's profile photo Alexander Kaiser
      Alexander Kaiser

      I think the error message is pretty clear. You need to enter a  file path to the mdb file, not the string "Microsoft Access Driver (*.mdb)"...

      As far as I can see from this blog, the file has to be accessible via NFS or be on a local file path. This is unfortunate, as we have to connect via tcp/ip.

      Author's profile photo Former Member
      Former Member
      Hi Sameer,

      Could you plz lemme know that, do we need to install Drivers for using Access Database as well in PI server.
      Because I'm getting the error as :DriverManagerException: Cannot establish connection to URL 'jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=//yhsapi01/PIApplications/Customer.mdb;': SQLException: [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x4c1c Thread 0x1ff4 DBC 0x5132024 Jet'

      Please letme know,

      Thanks in Advance

      Praveen

      Author's profile photo Former Member
      Former Member
      Error when attempting to get processing resources: com.sap.aii.af.service.util.concurrent.ResourcePoolException: Unable to create new pooled resource: DriverManagerException: Cannot establish connection to URL 'jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=//usr/sapxi/QAS/application/SAPXI/input': SAPClassNotFoundException: jdbc.odbc.JdbcOdbcDriver

      Please sugget.

      Author's profile photo diego quintana
      diego quintana
      Hi Sameer,

      I would like to ask for your assistance in determine the root cause of an issue I've been facing.

      Since I was not able to establish a direct connection between XI (running on Linux) and the MS Access DB I decided to use a J2SE Plain adapter (on a Windows platform) to accomplish the above mentioned it.

      However, the following error is obtained in the connection logs:

      : Attempt to access JDBC connection failed with "[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented"
      Attempt to initialize JDBC adapter failed

      The configuration parameters are as follow:

      JDBC Driver: sun.jdbc.odbc.JdbcOdbcDriver
      Connection: jdbc:odbc:Driver={Microsoft Access Driver *.mdb)};DBQ=C:/MyDB.mdb

      My first question is, the configuration you used as an example in this forum was made on a Linux environment?

      Did you ever face the error I’m describing above? If so, are you aware about any possible solution? Is this a JDBC driver error or a DB limitation?

      Thanks in advanced

      Kind regards

      Author's profile photo Former Member
      Former Member
      hi sameer,
      i have using JDBC to JDBC driver.in sender communication channel i have facing problem.Please help.

      Error during database connection to the database URL 'jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=/home/iqxadm/xifiles/mohittest/sender/Student_Database.mdb' using the JDBC driver 'sun.jdbc.odbc.JdbcOdbcDriver': 'com.sap.aii.adapter.jdbc.sql.DriverManagerException: Cannot establish connection to URL 'jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=/home/iqxadm/xifiles/mohittest/sender/Student_Database.mdb': java.lang.NullPointerException'

      Author's profile photo PhaniKumar Akella
      PhaniKumar Akella
      Hi All

      I have faced server related issues while configuring this scenario on PI running on UNIX server. Same scenario works great when configured on PI running on Windows. In case of any such issues please follow the below forum. Thank You.

      Re: PI Server Issues after executing simple File to DB (MS Access) scenario