Skip to Content

In case you are facing issue connecting MS Access with SAP PI, this document might be useful to you. This provides a step by step approach to resolve “driver not found” issue and specially written for PI users using Windows 7 (64 bits system). But for others also, this may have some information which will guide to reach to the actual root cause.

Below is the error message:

Error when attempting to get processing resources: com.sap.aii.af.lib.util.concurrent.ResourcePoolException: Unable to create new pooled resource: DriverManagerException: Cannot establish connection with the registered driver. sun.jdbc.odbc.JdbcOdbcDriver returns: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. : SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

First thought came to our mind when we started connecting SAP PI with MS Access, that we don’t need any driver installation as our operating system was windows and it bundles ODBC driver along with it. But, when I configured my communication channel(CC), I realized that my CC was not able to identify registered driver and some driver had to be installed. To verify it I checked my odbc administrator from Control panelà administrative tools-à ODBC and under driver tab I could see only two sql driver. So it made it clear that driver is missing.

While searching for this issue on internet I read through several blogs and forum and it was clear to me that if your Java version in 32 bits then your ODBC administrator would be under C:\Windows\SysWOW64 dir and I checked this dir and I found drivers were installed there but when I checked java version called by my application carefully I found that is 64 bits jdk. So that mean my application is calling ODBC located at C:\Windows\System32.

Now in case you are facing this error please follow below steps-

  • Check your OS version- In case it is 64 bits that means you have two ODBC driver present

          One under – 32 bits ODBC is at C:\Windows\SysWOW64

          Another under – 64 bits ODBC is at C:\Windows\System32

  • Check your JDK version. In case you have 32 as well as 64 bits both, please ensure which one is used by your Sap PI 
  • In case you have SAP pi using 32 bits java version then CC will pick up ODBC driver reference from C:\Windows\SysWOW64

/wp-content/uploads/2012/07/1_116127.png

  

  • If you are using 64 bits jdk then your Communication channel is looking for ODBC driver at C:\Windows\System32

    /wp-content/uploads/2012/07/2_116128.png

  • In my case under C:\Windows\SysWOW64 dir when I opened odbcad32.exe, I found all the drivers were already present. 

  /wp-content/uploads/2012/07/3_116129.png 

  • But as my JDK was not 32 bits so it was not taking driver refernce from this location.instead it was looking at C:\Windows\Sy stem32 and there my ODBC administrator was not showing any access driver

/wp-content/uploads/2012/07/4_116131.png

  •   After installation Access driver was visible under ODBC administrator

/wp-content/uploads/2012/07/5_116132.png  

  • I had configured my communication channel as per the blog by sameer-

http://scn.sap.com/people/sameer.shadab/blog/2005/10/24/connecting-to-ms-access-using-receiver-jdbc-adapter-without-dsn

          My configuration was as follow-

                    JDBC driver : sun.jdbc.odbc.JdbcOdbcDriver

                    connection : jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ= path\db.mdb

  • With above configuration I was still getting some error and then I changed my DBQ path to absolute path which was earlier pointing to mapped drive.
  • But error was still there and the final step, which resolved the error, was changing the connection string as below. For 64 bits connection it should have ‘*.accdb ‘ as well.

                                         jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=

                                   to

                                  jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=

References-

http://social.msdn.microsoft.com/Forums/is/adodotnetdataproviders/thread/4112c367-a196-4f26-bb55-009a1601f96c 

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/ef10d625-80d9-4666-b33e-d2f27fde9db5

To report this post you need to login first.

1 Comment

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

  1. Roy D

    Hi,

    A question, is this blog based on a SAP PI installation on OS Windows? or Unix?

    With my current PI system on UNIX I am having problems connect to my MS Access connection to a Windows system, where MS Access is installed.

    Also MS Access PC has no SQL Server installed, if I am correct, only a JDBC configuration is necessary right?

    (0) 

Leave a Reply