Skip to Content
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.
To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

      1. Mohit Verma
        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’

        (0) 
  1. 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

    (0) 
  2. Puneet Singhal
    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

    (0) 
  3. surya venkat
    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

    (0) 
  4. PRAVEEN KURNI
    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

    (0) 
  5. nutan champia
    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.

    (0) 
  6. 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

    (0) 
  7. Mohit Verma
    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’

    (0) 

Leave a Reply